SELECT *
INTO # FROM (
SELECT 'A' A,'B' B UNION
SELECT 'B','C' UNION
SELECT 'C','D' UNION
SELECT 'E','F' UNION
SELECT 'F','G'
)Z
;WITH Cte
AS(
SELECT *,[Level]=0 FROM # AS a WHERE NOT EXISTS(SELECT 1 FROM # WHERE B=a.A)
UNION ALL
SELECT a.*,[Level]=b.[Level]+1 FROM # AS a INNER JOIN Cte AS b ON b.B=a.A
)
SELECT * FROM Cte ORDER BY 1
/*
A B 0
B C 1
C D 2
E F 0
F G 1*/
INTO # FROM (
SELECT 'A' A,'B' B UNION
SELECT 'B','C' UNION
SELECT 'C','D' UNION
SELECT 'E','F' UNION
SELECT 'F','G'
)Z
;WITH Cte
AS(
SELECT *,[Level]=0 FROM # AS a WHERE NOT EXISTS(SELECT 1 FROM # WHERE B=a.A)
UNION ALL
SELECT a.*,[Level]=b.[Level]+1 FROM # AS a INNER JOIN Cte AS b ON b.B=a.A
)
SELECT * FROM Cte ORDER BY 1
/*
A B 0
B C 1
C D 2
E F 0
F G 1*/
解决方案 »
- sql server 2008 怎么导入ldf mdf文件
- 如何在企业管理器里查找某个表?
- windows2003下安装oracle8i到配置工具net8配置时过不去
- 请教:如何查询以指定字符开头和数字结尾的字符串
- 求一条SQL语句
- 数据库中的密码字段,需要对其进行加密,但加密算法该写到什么地方???
- 如何清理sp_trace_create 文件
- 安装sql2000问题,各位帮帮我(在线等,急)
- 一样的网站和数据内容,我换了个空间访问速度就变的特慢,是什么原因?
- 调用储存过程插入日期的问题!
- sql server8 r2 report service报错 身份验证扩展插件出现异常,或者返回了无效的值: 。 (rsAuthenticationExten
- 请教大家一个SQL分类统计的SQL语句
;WITH CTE AS(
SELECT T1.*,0[LEVEL],T1.A [ParentName] FROM # T1 LEFT JOIN # T2 ON T1.A=T2.B WHERE T2.A IS NULL
UNION ALL
SELECT T1.*,T2.[LEVEL]+1,T2.[ParentName] FROM # T1 JOIN CTE T2 ON T1.A=T2.B
)
SELECT * FROM CTE ORDER BY A