sqlserver 書查詢 之二

標籤：order by   nio   sele   replica   rom   base   SQ   select   join   

WITH tree
AS
(
SELECT ParentId, Organization_ID,1 AS x2level,Organization_Name,
CAST(Organization_Name AS NVARCHAR(max)) x2name,
CAST(+Organization_ID AS NVARCHAR(max)) x2id
FROM base_organization
WHERE ParentId=‘0‘
UNION ALL
SELECT c.ParentId, c.Organization_ID, tree.x2level + 1,c.Organization_Name,
CAST(REPLICATE(‘-‘, x2level * 4) + c.Organization_Name AS NVARCHAR(max)) x2name,
tree.x2id +‘:|:‘+ CAST(c.Organization_ID AS NVARCHAR(max)) x2id
FROM dbo.base_organization c
INNER JOIN tree
ON c.ParentId = tree.Organization_ID
)
SELECT x2name, Organization_ID, ParentId,x2level FROM tree
ORDER BY x2id;

sqlserver 書查詢 之二