mysql - 多个子查询，计算更多儿子但有条件

Question

你好!这工作正常，但我需要计算更多的儿子，但有一个条件

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

我尝试了这个，但收到以下错误...

SQL ERROR: Operand should contain 1 column(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 |
+----+--------+------+-------+------+------+---------+------+

基本上有多少个 child 以及每种类型有多少个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 = ?;