从SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,这对查询树形或层次结构的数据很有用。CTE即公用表表达式,虽然不恰当,但你可以将它看做成一个临时命名的结果集合。
示例数据
递归查树
WITH tProductPrice(id,pid,pname,level) AS ( SELECT id,pid,pname,0 level FROM dbo.ProductPrice WHERE pid=0 UNION ALL SELECT a.id,a.pid,a.pname,b.level+1 FROM dbo.ProductPrice a,tProductPrice b WHERE a.pid=b.id ) SELECT * FROM tProductPrice
查找父节点
DECLARE @id INT SET @id=7 WITH tuProductPrice AS ( SELECT id,pid,pname FROM dbo.ProductPrice WHERE id=@id UNION all SELECT a.id,a.pid,a.pname FROM dbo.ProductPrice a,tuProductPrice b WHERE a.id=b.pid ) SELECT * FROM tuProductPrice
评论前必须登录!
注册