树形结构有关的SQL

 1.  SQL 递归查询（根据指定的节点 --> 向上 获取所有父节点）

WITH TEMP AS 
(
SELECT * FROM chpTur WHERE ID = '5' --表的ID
UNION ALL 
SELECT T0.* FROM TEMP,chpTur T0 WHERE TEMP.ID=T0.prID --子级ID==父级ID
)
SELECT * FROM TEMP;

 

2.  SQL 递归查询（根据指定的节点 向下 --> 获取所有子节点）

WITH TEMP AS 
(
SELECT * FROM chpTur WHERE ID = '800' --表的ID
UNION ALL 
SELECT T0.* FROM TEMP,chpTur T0 WHERE TEMP.prID=T0.ID --父级ID==子级ID
)
SELECT * FROM TEMP;

 

 3.  路径的遍历

WITH T AS
(
SELECT *, convert(varchar(200), PLMing0) as PLLJing FROM chpTur WHERE prID = 0
UNION ALL 
SELECT a.*, convert(varchar(200), b.PLLJing + '→' + a.PLMing0) FROM chpTur a join T b on a.prID = b.ID
)
SELECT    * FROM T

 

4.  首节点 (一级节点)

SELECT * FROM chpTur A 
WHERE NOT EXISTS (SELECT * FROM chpTur B WHERE a.prID=b.ID)

 

5.  最后节点 (N 级节点)

SELECT * FROM chpTur A 
WHERE NOT EXISTS (SELECT * FROM chpTur B WHERE a.ID=b.prID)

 

6.  分类路径分割 （路径符 替换 Tab）

SELECT 
tTur.*
  , vPath.PLLJing 
  , REPLACE(vPath.PLLJing , '→', CHAR(9) ) AS PLLJing_B
FROM chpTur AS tTur
LEFT JOIN vPLLJing AS vPath ON tTur.ID = vPath.ID
ORDER BY prID, PLMing0

 

 7. 涉及到所选分类的 分类ID 集合

WITH TEMP AS 
(
SELECT ID, prID FROM chpTur WHERE ID = 2 
UNION ALL 
SELECT T0.ID, T0.prID FROM TEMP,chpTur T0 WHERE TEMP.ID=T0.prID AND sdRecFlg = 'N'
) 
SELECT DISTINCT ID FROM TEMP

 

8.  chpTur Fix prID  --- By  prLinkID

UPDATE A 
SET  A.prID = B.ID    
FROM chpTur as A, chpTur B    
WHERE A.prLinkID = B.LinkID
     AND A.prLinkID <> ''  

 chpInf Fix turID By turLinkID

update chpInf
  set  chpInf.turID = chpTur.ID
 from  chpInfo as chpInf, chpTur as chpTur 
where  chpInf.turLinkID = chpTur.LinkID

 

 

9.  校验无上一级的类别

select * from chpTur
where ID > 1
AND prID not in (select ID from chpTur)

 

sql自关联查询上级id对应的名字

 

方法一：关联查询

select ci1.id,(select ci2.name from city_info ci2 where ci2.id = ci1.pId) as pName,ci1.name,ci1.title,ci1.type from city_info ci1;

 

    方法二：左外连接查询

select ci1.id,ci2.name pName,ci1.name,ci1.title,ci1.type from city_info ci1 LEFT JOIN city_info ci2 ON ci1.pId = ci2.id;

   查询结果