create table A(aID int identity(1,1),baseid int,[name] varchar(20))
create table B(bID int identity(1,1),parentId int,childId int)insert A
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc' union all
select 4,'a1' union all
select 5,'a2' union all
select 6,'b1' union all
select 7,'aa1' union all
select 8,'dsdssd' insert B
select 1,4 union all
select 1,5 union all
select 4,7 union all
select 7,8注:A.baseId = B.parentId
表B中的childId对应A表的Id,意思就是说A表存了所有数据,包括所以节点,而B只存有关系.
树结构如下:
---1
---4
---7
---8
---5
---2
---3
---6
现在假如获取顶层节点和第一子节点,请问怎么解决。
SELECT *
FROM A LEFT JOIN b
ON A.baseId = B.parentIdparentid 为null的不就是顶层的咯?至于第一子节点.....你要一起查出来?那展示的结果是如何的?
希望显示结果1,2,3,4,5,6 顺序无所谓/
B表数据是全的,比如A表中2在B表中不存在 则说明2是顶层
--create table B(bID int identity(1,1),parentId int,childId int)
--insert A
--select 1,'aaa' union all
--select 2,'bbb' union all
--select 3,'ccc' union all
--select 4,'a1' union all
--select 5,'a2' union all
--select 6,'b1' union all
--select 7,'aa1' union all
--select 8,'dsdssd'
--insert B
--select 1,4 union all
--select 1,5 union all
--select 4,7 union all
--select 7,8
WITH cte AS
(
SELECT parentId ,childId,1 [LEVEL]
FROM b
WHERE parentid=1
UNION ALL
SELECT b.parentid,b.childId,a.[level]+1
FROM cte a INNER JOIN B ON B.parentid=a.childId
)
--SELECT * FROM cte --把这里取消注销可以看到CTE的结果
--/*
SELECT DISTINCT parentid baseid FROM cte WHERE [LEVEL] IN (1 ,2)
UNION
select DISTINCT childId baseid FROM cte WHERE [LEVEL] IN (1 ) --由于要过滤第三层,所以这里只查询1
UNION
SELECT * FROM (
SELECT baseid
FROM A
WHERE baseid NOT IN (
SELECT parentId
FROM B
UNION
SELECT childId
FROM b
) )b
/*
baseid
-----------
1
2
3
4
5
6
(6 行受影响)
*/
--create table B(bID int identity(1,1),parentId int,childId int)
--insert A
--select 1,'aaa' union all
--select 2,'bbb' union all
--select 3,'ccc' union all
--select 4,'a1' union all
--select 5,'a2' union all
--select 6,'b1' union all
--select 7,'aa1' union all
--select 8,'dsdssd'
--insert B
--select 1,4 union all
--select 1,5 union all
--select 4,7 union all
--select 7,8
WITH cte
AS ( SELECT parentId ,
childId ,
1 [LEVEL] --定义级别为顶层
FROM b
WHERE parentid = 1
UNION ALL
SELECT b.parentid ,
b.childId ,
a.[level] + 1 --计算B中的个个级别
FROM cte a
INNER JOIN B ON B.parentid = a.childId
)
--SELECT * FROM cte --这步可以看到CTE的运行结果
SELECT DISTINCT
parentid baseid
FROM cte
WHERE [LEVEL] IN ( 1, 2 ) --选择parentid列中级别为1、2的数据
UNION
SELECT DISTINCT
childId baseid
FROM cte
WHERE [LEVEL] IN ( 1 ) --选择childid列中级别为1的数据,由于为2 的时候会把7也引出来
UNION
--筛选在A中但不在B中的数据
SELECT *
FROM ( SELECT baseid
FROM A
WHERE baseid NOT IN ( SELECT parentId
FROM B
UNION
SELECT childId
FROM b )
) b
/*
baseid
-----------
1
2
3
4
5
6
(6 行受影响)
*/
from A
where aid not in (select parentid from B union select childid from B)
union all
select * --有子顶层
from A where aID in (select distinct parentId from B where not parentId in (select childid from B))
union all
select * --第一个子
from A where aID in (select distinct childId from B where parentId in (select distinct parentId from B where not parentId in (select childid from B)))
实际存的Guid,ID只是举例
cte执行过程是怎么样的啊。一直没有搞懂,所以写的乱七八糟的。
A的顶层节点在B中应该是无记录的。
要想获得数据,必须连A了
刚才试了一下你写的sql
我在优化下,看了执行计划,听夸张的。