我有一個表源
idGeo GEO PARENTID
1 EMEA NULL
2 France 1
3 mIDCAPSfRANCE 2
4 Germany 1
5 France exl midcaps 2
6 Amercias NULL
7 US 6
層次結構的預期結果
我試圖做左加入（自我加入），但我無法完全達到預期。
uj5u.com熱心網友回復：
這是一個通用方法，無論層次結構的級別如何。
SQL
-- DDL and sample data population, start
DECLARE @tbl table (
idGeo INT IDENTITY PRIMARY KEY,
GEO VARCHAR(64),
PARENTID INT
);
insert into @tbl (GEO, PARENTID) values
( 'EMEA', NULL),
( 'France', 1),
( 'mIDCAPSfRANCE', 2),
( 'Germany', 1),
( 'France exl midcaps', 2),
( 'Amercias', NULL),
( 'US', 6);
-- DDL and sample data population, end
--SELECT * FROM @tbl;
WITH cte AS
(
-- Anchor query
SELECT idGEO, GEO, ParentID, 1 AS [Level]
, CAST('/' GEO AS VARCHAR(1000)) AS XPath
FROM @tbl
WHERE ParentID IS NULL
UNION ALL
-- Recursive query
SELECT t.idGEO, t.GEO, t.ParentID, cte.[Level] 1 AS [Level]
, CAST(cte.[XPath] '/' t.GEO AS VARCHAR(1000)) AS [XPath]
FROM @tbl AS t
INNER JOIN cte ON t.ParentID = cte.idGEO
WHERE t.ParentID IS NOT NULL
)
SELECT idGEO
, REPLICATE(' ',[Level]-1) GEO AS GEOHierarchy
, GEO, ParentID, [Level], [XPath]
FROM cte
ORDER BY XPath;
輸出
uj5u.com熱心網友回復：
因此，如果我了解您想隨著關卡的進行生成列。您不能動態生成列，SQL 是一種固定列語言。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/474604.html
上一篇：查找不重復的列值組合