多个子查询，多个子查询，但有一个条件

Question

嗨!这很好，但我需要更多的儿子，但有条件。

select t1.*,(select count(*)
 from tabl t2 
 where t2.tabl = t1.id) as  sons
 from tabl t1 where tabl.sons = ?;

我试过，但是收到以下错误..。

SQL错误:操作数应包含1列(S)

 select t1.*,(select count(*),(select count(*) from tbl1 t3 where t3.type = 3) as Johnny
 from tabl t2 
 where t2.tabl = t1.id) as  sons
 from tabl t1 where tabl.sons = ?;

拜托，谢谢

编辑:这正是我想做的，但我认为这不是正确的方法(我更改了名字对不起，但看起来更好)

select m1.*,
(select count(*) from node m2 where m2.nodeid = m1.id) as child,
(select count(*) from node m3 where m3.type = 3 and m3.nodeid = m1.id) as del,
(select count(*) from node m3 where m3.type = 1 and m3.nodeid = m1.id) as edit,
(select count(*) from node m3 where m3.type = 4 and m3.nodeid = m1.id) as protect,
(select count(*) from node m3 where m3.type = 2 and m3.nodeid = m1.id) as move  
from node m1 where nodeid = ?

+----+--------+------+-------+------+------+---------+------+
| id | nodeid | type | child | del  | edit | protect | move |
+----+--------+------+-------+------+------+---------+------+
| 1  |   null |    0 |     3 |    0 |    1 |       1 |    1 |
+----+--------+------+-------+------+------+---------+------+

+----+--------+------+-------+------+------+---------+------+
| id | nodeid | type | child | del  | edit | protect | move |
+----+--------+------+-------+------+------+---------+------+
| 34 |      1 |    1 |     7 |    2 |    1 |       1 |    1 |
| 23 |      1 |    2 |     3 |    1 |    0 |       0 |    0 |
| 32 |      1 |    3 |     2 |    0 |    0 |       0 |    0 |
+----+--------+------+-------+------+------+---------+------+

基本上，每种类型的xd有多少个孩子？

Answer 1

MySQL 8具有窗口函数。因此，您可以这样编写查询：

SELECT t1.*, COUNT(*) OVER(PARTITION BY t1.id ORDER BY t1.id) AS sons,
       COUNT(CASE WHEN t1.type = 3 THEN t1.type END) OVER (PARTITION BY t1.id ORDER BY t1.id) AS Johnny
FROM tabl t1
WHERE t1.sons = ?;    

Answer 2

不能将多列定义为一列。

(select count(*),(select count(*) from tbl1 t3 where t3.type = 3) as Johnny
 from tabl t2  where t2.tabl = t1.id) as  sons 

儿子:一列，伯爵(*)，，，约翰尼:多列

请将其更改为以下sql..。

(select count(*) from tabl t2 where t2.tabl=t1.id), (select count(*) from tbl1 t3 where t3.type=3)

Answer 3

这就是你要找的吗？

select t.*,
       count(*) over (partition by id) as sons,
       sum(type = 3) over (partition by id) as johnny
from tabl t
having sons = ?;

MySQL扩展了having子句，以便在非聚合查询中对表别名进行筛选。