mysql - 根据子节点的数量获取父节点的数量

Question

我有具有以下结构的表格。

AD_TABLE -
              ID|NAME|CAT_ID|TYPE
               1| car | C0101|Sale
               2|bike | C0201|Want
CAT_TABLE -
              ID   |NAME          |PARENT|LEVEL
              C0100|Vehicle       |C0100 |    0
              C0101|Car           |C0100 |    1
              C0200|Bike/Scooters |C0100 |    1
              C0201|Bike          |C0200 |    2
              C0202|Scooter       |C0200 |    2

我需要获取每个类别的广告数。我正在使用以下查询

SELECT     `CAT_TABLE`.`ID`,`CAT_TABLE`.`NAME`,`CAT_TABLE`.`LEVEL`,`CAT_TABLE`.`PARENT`,      COUNT(`AD_TABLE`.`ID`) 
    FROM       `CAT_TABLE` 
    LEFT JOIN  `AD_TABLE` 
    ON         `AD_TABLE`.`CAT_ID`=`CAT_TABLE`.`ID`
    AND        `AD_TABLE`.`TYPE`='0'
    WHERE      (`CAT_TABLE`.`ID`='C0100' OR `CAT_TABLE`.`PARENT`='C0100')
    GROUP BY   `CAT_TABLE`.`ID`

这使得车辆、自行车/踏板车这些主要类别的计数为 0。我需要这些类别来获取相应子类别的计数。例如:车辆数应为汽车 + 自行车/踏板车的总数，自行车/踏板车的总数应为自行车 + 踏板车的总数。我仍在研究高级 SQL 连接，因此我们将不胜感激。

Answer 1

使用 UNION 将 AD_TABLE 中的项目与 CAT_TABLE 中的子类别组合起来

SELECT c.ID, c.NAME, c.LEVEL, c.PARENT, COUNT(a.ID)
FROM CAT_TABLE c
LEFT JOIN (SELECT ID, CAT_ID
           FROM AD_TABLE
           WHERE TYPE = '0'
           UNION ALL
           SELECT ID, PARENT
           FROM CAT_TABLE
           WHERE LEVEL != 0) a
ON a.CAT_ID = c.ID
GROUP BY c.ID

SQLFIDDLE

有关在 SQL 中管理分层数据的更多建议，请参阅 here .

请注意，我的查询仅计算类别的直接子节点，而不计算后代叶节点。使用该页面中的术语，您的架构是邻接列表模型，并且很难在 SQL 中处理多个级别 - 它没有循环或递归。如果您转换为嵌套集模型，他将展示如何通过简单的查询来计算所有终端节点的数量。