求一组复杂SQL语句。

Question

--准备数据create table table1(
class_id     int,
parent_id       int,
[level]        int,
name varchar(100)
)
goinsert table1
select
1,            0,               1,              'test1'
union all select
2,            1,               2,              'test2'
union all select
3,            1,               2,              'test3'
union all select
4,            2,               3,              'test4'go--建立函数@Paid是@id的祖先，返回1，否则返回0create function isPa(@id int,@Paid int)
returns bit
as
begin
declare @pId int
set @pId=@id
while @pId<>0 
begin
if @pId=@Paid return 1
select @pId=parent_id  from table1 where class_id=@pId
end
return 0
end
go--查询语句
select a.class_id,b.class_id as c1_id,b.name as c1_name,c.class_id as c2_id,c.name as c2_name,d.class_id as c3_id,d.name as c3_name,e.class_id as c4_id,e.name as c4_name
from table1 a left join table1 b
on b.[level]=1 and dbo.isPa(a.class_id,b.class_id)=1
left join table1 c
on c.[level]=2 and dbo.isPa(a.class_id,c.class_id)=1
left join table1 d
on d.[level]=3 and dbo.isPa(a.class_id,d.class_id)=1
left join table1 e
on e.[level]=4 and dbo.isPa(a.class_id,e.class_id)=1--结果：class_id    c1_id       c1_name                                                                                              c2_id       c2_name                                                                                              c3_id       c3_name                                                                                              
----------- ----------- ---------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
1           1           test1                                                                                                NULL        NULL                                                                                                 NULL        NULL
2           1           test1                                                                                                2           test2                                                                                                NULL        NULL
3           1           test1                                                                                                3           test3                                                                                                NULL        NULL
4           1           test1                                                                                                2           test2                                                                                                4           test4（所影响的行数为 4 行）

Answer 1

name 改成 varchar(5)的结果：
class_id    c1_id       c1_name c2_id       c2_name c3_id       c3_name c4_id       c4_name 
----------- ----------- ------- ----------- ------- ----------- ------- ----------- ------- 
1           1           test1   NULL        NULL    NULL        NULL    NULL        NULL
2           1           test1   2           test2   NULL        NULL    NULL        NULL
3           1           test1   3           test3   NULL        NULL    NULL        NULL
4           1           test1   2           test2   4           test4   NULL        NULL（所影响的行数为 4 行）

Answer 2

--级别最多四级吗?--创建处理函数
create function f_id()
returns @re table(class_id int,id_str varchar(8000),level int)
as
begin
declare @l int
set @l=1
insert @re select class_id,class_id,level
from 表 
where level=@l while @@rowcount>0
begin
set @l=@l+1
insert @re select a.class_id,b.id_str+','+cast(a.class_id as varchar),a.level
from 表 a join @re b on a.parent_id=b.class_id
where a.level=@l
end
return
end
go--查询
select a.class_id
,c1_id=max(case b.level when 1 then b.class_id end)
,c1_name=max(case b.level when 1 then c.name end)
,c2_id=max(case b.level when 2 then b.class_id end)
,c2_name=max(case b.level when 2 then c.name end)
,c3_id=max(case b.level when 3 then b.class_id end)
,c3_name=max(case b.level when 3 then c.name end)
,c4_id=max(case b.level when 4 then b.class_id end)
,c4_name=max(case b.level when 4 then c.name end)
from f_id() a 
join f_id() b on a.id_str like b.id_str+'%'
join 表 c on b.class_id=b.class_id and b.level=c.level
group by a.class_id

Answer 3

--测试--测试数据
create table 表(class_id int,parent_id int,[level] int,name varchar(50))
insert 表 select 1,0,1,'test1'
union all select 2,1,2,'test2'
union all select 3,1,2,'test3'
union all select 4,2,3,'test4'
go--创建处理函数
create function f_id()
returns @re table(class_id int,id_str varchar(8000),level int)
as
begin
declare @l int
set @l=1
insert @re select class_id,class_id,level
from 表 
where level=@l while @@rowcount>0
begin
set @l=@l+1
insert @re select a.class_id,b.id_str+','+cast(a.class_id as varchar),a.level
from 表 a join @re b on a.parent_id=b.class_id
where a.level=@l
end
return
end
go--查询
select a.class_id
,c1_id=max(case b.level when 1 then b.class_id end)
,c1_name=max(case b.level when 1 then c.name end)
,c2_id=max(case b.level when 2 then b.class_id end)
,c2_name=max(case b.level when 2 then c.name end)
,c3_id=max(case b.level when 3 then b.class_id end)
,c3_name=max(case b.level when 3 then c.name end)
,c4_id=max(case b.level when 4 then b.class_id end)
,c4_name=max(case b.level when 4 then c.name end)
from f_id() a 
join f_id() b on a.id_str like b.id_str+'%'
join 表 c on b.class_id=b.class_id and b.level=c.level
group by a.class_id
go--删除测试
drop table 表
drop function f_id/*--测试结果class_id    c1_id       c1_name    c2_id       c2_name    c3_id       c3_name    c4_id       c4_name  
----------- ----------- ---------- ----------- --------- ------------ --------- ----------- ---------------
1           1           test1      NULL        NULL       NULL        NULL       NULL        NULL
2           1           test1      2           test3      NULL        NULL       NULL        NULL
3           1           test1      3           test3      NULL        NULL       NULL        NULL
4           1           test1      2           test3      4           test4      NULL        NULL（所影响的行数为 4 行）
--*/

Answer 4

--如果级别不只四级,同函数不变,改查询方法为动态的就可以了
--创建处理函数(与上面的一样,不需要做改变)
create function f_id()
returns @re table(class_id int,id_str varchar(8000),level int)
as
begin
declare @l int
set @l=1
insert @re select class_id,class_id,level
from 表 
where level=@l while @@rowcount>0
begin
set @l=@l+1
insert @re select a.class_id,b.id_str+','+cast(a.class_id as varchar),a.level
from 表 a join @re b on a.parent_id=b.class_id
where a.level=@l
end
return
end
go--查询,改动态SQL语句处理
declare @s varchar(8000),@i int
select @s='',@i=max(level) from 表
while @i>0
select @s=',c'+cast(@i as varchar)
+'_id=max(case b.level when '+cast(@i as varchar)
+' then b.class_id end),c'++cast(@i as varchar)
+'_name=max(case b.level when '++cast(@i as varchar)
+' then c.name end)'+@s
,@i=@i-1
exec('select a.class_id'+@s+'
from f_id() a 
join f_id() b on a.id_str like b.id_str+''%''
join 表 c on b.class_id=b.class_id and b.level=c.level
group by a.class_id
')