代码之家 › 专栏 › 技术社区 › Abu Ayyub

mysql上同一表的count子菜单

count mysqli sql mysql php

Abu Ayyub · 技术社区 · 8 年前

menuId     | menuName  | menuType  | menuParentId  
-------------------------------------------------
1          | Settings  | parent    | NULL          
2          | Set1      | child     | 1             
3          | Set2      | child     | 1             
4          | Category  | parent    | NULL         
5          | Cat1      | child     | 4             
6          | Cat2      | child     | 4             
7          | Dashboard | parent    | NULL

menuId     | menuName  | countChild | open 
--------------------------------------------
1          | Settings  | 2          | true           
2          | Set1      | 0          | false                            
3          | Set2      | 0          | false                            
4          | Category  | 2          | true                       
5          | Cat1      | 0          | false                            
6          | Cat2      | 0          | false                           
7          | Dashboard | 0          | false

谢谢你的帮助

1 回复 | 直到 8 年前

M Khalid Junaid 8 年前

open case 聚合函数结果的语句 COUNT

SELECT a.menuId,
       a.menuName,
       COUNT(b.menuId) countChild,
       CASE WHEN COUNT(b.menuId) > 0 THEN TRUE ELSE FALSE END `open`
FROM table1 a
LEFT JOIN table1 b ON a.menuId = b.menuParentId
GROUP BY a.menuId, a.menuName

Demo

qoutes