如何实现这样的查询
ID名称父项
1类一0
2类二0
3项一1
4项二1
5项三2
6项四2
7类三0
8项五7
9项61
如何通过SQL查询形成如下格式
1类一0
3项一1
4项二1
9项六1
2类二0
5项三2
6项四2
7类三0
8项五7
[解决办法]
只有两层可以
select a.* from tablename a left join tablename b
on a.父项=b.ID
order by isnull(b.id,a.id),a.id
[解决办法]
create table #([ID] int,[name] nvarchar(10),pID int)
insert into #
select 1, '类一 ',0 union all
select 2, '类二 ',0 union all
select 3, '项一 ',1 union all
select 4, '项二 ',1 union all
select 5, '项三 ',2 union all
select 6, '项四 ',2 union all
select 7, '类三 ',0 union all
select 8, '项五 ',7 union all
select 9, '项六 ',1
select A.* from # A left join # B on a.pID = b.[ID]
order by isnull(B.[ID],A.[ID]),A.[ID]
[解决办法]
if object_id( 'temp ')> 0 drop table temp
create table temp(ID int,[名称] varchar(20),[父项] int)
insert into temp
select 1, '类一 ',0
union all
select 2, '类二 ',0
union all
select 3, '项一 ',1
union all
select 4, '项二 ',1
union all
select 5, '项三 ',2
union all
select 6, '项四 ',2
union all
select 7, '类三 ',0
union all
select 8, '项五 ',7
union all
select 9, '项六 ',1
select * from temp
order by case when [父项]=0 then ID else [父项] end,id
/*
1类一0
3项一1
4项二1
9项六1
2类二0
5项三2
6项四2
7类三0
8项五7
*/
drop table temp
