关于一个分级汇总的问题..
有多少级是不确定的,
ID PID Name
1 null A
2 1 B
3 1 C
4 3 D
5 4 E
: : :
要求结果为:
ID Name1 Name2 Name3 Name4 ....
1 A
2 A B
3 A C
4 A C D
5 A C D E
---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
CREATE TABLE T(ID INT,PID INT,Name VARCHAR(1))
INSERT INTO T
SELECT 1,null,'A' UNION ALL
SELECT 2,1,'B' UNION ALL
SELECT 3,1,'C' UNION ALL
SELECT 4,3,'D' UNION ALL
SELECT 5,4,'E'
--SQL查询如下:
--2005写法
DECLARE @i INT
;WITH Liang AS
(
SELECT
*,
PATH=CAST(Name AS VARCHAR(MAX))
FROM T
WHERE PID IS NULL
UNION ALL
SELECT
B.*,
A.PATH+'.'+B.Name
FROM Liang AS A
JOIN T AS B
ON B.PID=A.ID
)
SELECT @i=MAX(LEN(PATH)-LEN(REPLACE(PATH,'.','')))+1
FROM Liang
DECLARE @SQL VARCHAR(MAX)
DECLARE @n INT
SELECT @SQL='',@n=1
WHILE @n<=@i
BEGIN
SELECT @SQL=@SQL+',xml_data.value(''(/v)['+RTRIM(@n)+']'',''varchar(20)'') AS [Name'+RTRIM(@n)+']'
SET @n=@n+1
END
EXEC('
;WITH Liang AS
(
SELECT
*,
PATH=CAST(Name AS VARCHAR(MAX))
FROM T
WHERE PID IS NULL
UNION ALL
SELECT
B.*,
A.PATH+''.''+B.Name
FROM Liang AS A
JOIN T AS B
ON B.PID=A.ID
)
SELECT
ID'+@SQL+'
FROM (
SELECT *,
xml_data=CONVERT(xml,''<v>''+REPLACE(PATH,''.'',''</v><v>'')+''</v>'')
FROM Liang
) AS A
')
DROP TABLE T
--2000写法
--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(20)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO
--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@PID INT)
RETURNS @t_Level TABLE(ID INT,PID INT,Name VARCHAR(20),Level int,PATH VARCHAR(8000))
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level
SELECT ID,PID,Name,@Level,Name
FROM T
WHERE PID=@PID
OR (@PID IS NULL AND PID IS NULL)
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level
SELECT a.ID,a.PID,a.Name,@Level,B.PATH+'.'+a.Name
FROM T a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
DECLARE @i INT
SELECT @i=MAX(LEN(PATH)-LEN(REPLACE(PATH,'.','')))+1
FROM dbo.f_Cid(NULL)
DECLARE @SQL VARCHAR(8000)
DECLARE @n INT
SELECT @SQL='',@n=1
WHILE @n<=@i
BEGIN
SELECT @SQL=@SQL+',dbo.f_GetStr(PATH,'+RTRIM(@n)+',''.'') AS [Name'+RTRIM(@n)+']'
SET @n=@n+1
END
EXEC ('
SELECT
ID'+@SQL+'
FROM dbo.f_Cid(NULL) AS A
')
GO
DROP FUNCTION dbo.f_GetStr,dbo.f_Cid
DROP TABLE T
/*
ID Name1 Name2 Name3 Name4
----------- -------------- ------------ ----------------------------
1 A NULL NULL NULL
2 A B NULL NULL
3 A C NULL NULL
4 A C D NULL
5 A C D E
(5 行受影响)
*/