原如数据是这样:
id value upid(父亲ID)22 电压(V) 0
23 220~250 22
24 110~130 22
25 36 22
26 功率(W) 0
27 10 26
28 15 26
29 25 26
30 30 22要求这样显示:
id value upid(父亲ID)22 电压(V) 0
23 220~250 22
24 110~130 22
25 36 22
30 30 22
26 功率(W) 0
27 10 26
28 15 26
29 25 26
id value upid(父亲ID)22 电压(V) 0
23 220~250 22
24 110~130 22
25 36 22
26 功率(W) 0
27 10 26
28 15 26
29 25 26
30 30 22要求这样显示:
id value upid(父亲ID)22 电压(V) 0
23 220~250 22
24 110~130 22
25 36 22
30 30 22
26 功率(W) 0
27 10 26
28 15 26
29 25 26
()
GO
CREATE TABLE TB(id INT ,value VARCHAR(10), upid INT)
INSERT INTO TB
SELECT 22 ,'电压(V)', 0 UNION ALL
SELECT 23 ,'220~250', 22 UNION ALL
SELECT 24 ,'110~130', 22 UNION ALL
SELECT 25 ,'36', 22 UNION ALL
SELECT 26 ,'功率(W)', 0 UNION ALL
SELECT 27 ,'10', 26 UNION ALL
SELECT 28 ,'15', 26 UNION ALL
SELECT 29 ,'25', 26 UNION ALL
SELECT 30 ,'30', 22;WITH MU AS (
SELECT *,CONVERT(VARCHAR(MAX),ROW_NUMBER() OVER(ORDER BY ID)) AS NID FROM TB WHERE UPID=0
UNION ALL
SELECT TB.*,MU.NID+'-'
FROM TB
INNER JOIN MU ON TB.UPID=MU.ID
)
SELECT ID,VALUE,UPID FROM MU ORDER BY NID
/*
22 电压(V) 0
23 220~250 22
24 110~130 22
25 36 22
30 30 22
26 功率(W) 0
27 10 26
28 15 26
29 25 26
*/