先有两张表a采取树型结构,表结构和数据如下,其中上级ID为0代表最上级
ID 上级ID 名称
1 0 北京市
2 1 北京一环
3 1 北京二环
4 0 上海市
5 4 上海虹口区
6 1 北京三环表b中的数据如下
ID 状态
2 1
2 1
3 2
5 1
4 3
3 1
6 1
1 2
5 2现要从B表中实现如下结果:ID 状态1条数 状态2条数 状态3条数 总计条数
北京市 4 2 0 6
上海市 1 1 1 3结果中的北京市包含它自己和其所有的孩子(比如:北京一环等)
请问这样的SQL该怎么写?
ID 上级ID 名称
1 0 北京市
2 1 北京一环
3 1 北京二环
4 0 上海市
5 4 上海虹口区
6 1 北京三环表b中的数据如下
ID 状态
2 1
2 1
3 2
5 1
4 3
3 1
6 1
1 2
5 2现要从B表中实现如下结果:ID 状态1条数 状态2条数 状态3条数 总计条数
北京市 4 2 0 6
上海市 1 1 1 3结果中的北京市包含它自己和其所有的孩子(比如:北京一环等)
请问这样的SQL该怎么写?
-- Author: liangCK 小梁
-- Date : 2008-11-13 19:47:47
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (ID INT,上级ID INT,名称 VARCHAR(10))
INSERT INTO @tb1
SELECT 1,0,'北京市' UNION ALL
SELECT 2,1,'北京一环' UNION ALL
SELECT 3,1,'北京二环' UNION ALL
SELECT 4,0,'上海市' UNION ALL
SELECT 5,4,'上海虹口区' UNION ALL
SELECT 6,1,'北京三环'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (ID INT,状态 INT)
INSERT INTO @tb2
SELECT 2,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,2 UNION ALL
SELECT 5,1 UNION ALL
SELECT 4,3 UNION ALL
SELECT 3,1 UNION ALL
SELECT 6,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 5,2--SQL查询如下:;WITH Liang
AS
(
SELECT ID,上级ID,名称,name=名称
FROM @tb1
WHERE 上级ID=0
UNION ALL
SELECT b.ID,b.上级ID,b.名称,c.名称
FROM Liang AS c
JOIN @tb1 AS b
ON c.ID=b.上级ID
)
SELECT a.name AS 名称,
SUM(CASE WHEN b.状态=1 THEN 1 ELSE 0 END) AS 状态1条数,
SUM(CASE WHEN b.状态=2 THEN 1 ELSE 0 END) AS 状态2条数,
SUM(CASE WHEN b.状态=3 THEN 1 ELSE 0 END) AS 状态3条数,
SUM(CASE WHEN b.状态=4 THEN 1 ELSE 0 END) AS 状态4条数,
COUNT(*) AS 总计条数
FROM Liang AS a
JOIN @tb2 AS b
ON a.ID=b.ID
GROUP BY a.name/*
名称 状态1条数 状态2条数 状态3条数 状态4条数 总计条数
---------- ----------- ----------- ----------- ----------- -----------
北京市 4 2 0 0 6
上海市 1 1 1 0 3(2 行受影响)
*/