求一组复杂SQL语句。

[billy_zh]

现在一类别表，字段如下：
class_id int, // 类别ID
parent_id int, // 父类别ID
[level] int, // 类别层次，取1-4，表示1到4级
name varchar(50) // 类别名称

现求一组SQL语句能实现以下输出：

示例数据：
class_id parent_id [level] name
1 0 1 test1
2 1 2 test2
3 1 2 test3
4 2 3 test4

输出:
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
其中c1_id, c1_name 为1级类别的ID和名称,
c2_id, c2_name 为2级类别的ID和名称,
c3_id, c3_name 为3级类别的ID和名称,
c4_id, c4_name 为4级类别的ID和名称.

[minajo21]

没看懂啊

[CCEO]

--准备数据

create table table1(
class_id int,
parent_id int,
[level] int,
name varchar(100)
)
go

insert 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，否则返回0

create 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 行）

[CCEO]

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 行）

[zjcxc]

--级别最多四级吗?

--创建处理函数
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

[zjcxc]

--测试

--测试数据
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 行）
--*/

[zjcxc]

--如果级别不只四级,同函数不变,改查询方法为动态的就可以了


--创建处理函数(与上面的一样,不需要做改变)
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
')

[lllqe]

up

[zhangzs8896]

强！