id parentid
1 root
2 root
3 2
4 1
5 1
6 1
7 2
8 root
9 root不要用存储过程,怎么用1个sql语句形成这样的结果,因为我要在mysql里用的,mysql的存储过程难用。1
4
5
6
2
3
7
8
9
1 root
2 root
3 2
4 1
5 1
6 1
7 2
8 root
9 root不要用存储过程,怎么用1个sql语句形成这样的结果,因为我要在mysql里用的,mysql的存储过程难用。1
4
5
6
2
3
7
8
9
insert into tb select 1,0
insert into tb select 2,0
insert into tb select 3,2
insert into tb select 4,1
insert into tb select 5,1
insert into tb select 6,1
insert into tb select 7,2
insert into tb select 8,0
insert into tb select 9,0
go
select (case when parentid>0 then ' ' else '' end)+ltrim(id) from(
select id,parentid,right('000'+ltrim(id),4) as pid from tb where parentid=0
union all
select id,parentid,right('000'+ltrim(parentid),4)+right('000'+ltrim(id),4)as pid from tb where parentid<>0
)t order by pid
go
drop table tb
/*
1
4
5
6
2
3
7
8
9
*/
" from sms_conf.project_list list1 \n"+
" where 1=1 \n"+
" and level=1 \n"+
" union all \n"+
" select id id2,parent code2 ,level level2 ,logindate logindate2 \n"+
" from sms_conf.project_list list1 \n"+
" where 1=1 \n"+
" and level=2 \n"+
" order by code2,level2,logindate2 "+
insert into 表 select 1,0
insert into 表 select 2,0
insert into 表 select 3,2
insert into 表 select 4,1
insert into 表 select 5,1
insert into 表 select 6,1
insert into 表 select 7,2
insert into 表 select 8,0
insert into 表 select 9,0
select b.id 列,a.id into # from 表 a join 表 b on a.parentid=b.id
order by 列,a.id
select 列= case when exists (select 1 from # where Id<a.id and a.列=列) then '' else a.列 end,a.id from # a
drop table #
(5 行受影响)
列 id
----------- -----------
1 4
0 5
0 6
2 3
0 7(5 行受影响)