zl程序教程

您现在的位置是:首页 >  数据库

当前栏目

树形结构有关的SQL

SQL 结构 有关 树形
2023-09-27 14:25:26 时间

 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)