22,209
社区成员
发帖
与我相关
我的任务
分享
create table data(id nvarchar(10),name nvarchar(10),parent nvarchar(10),hasChild int)
insert into data select '001','a','0',1
insert into data select '002','b','0',1
insert into data select '003','c','001',0
insert into data select '004','d','001',0
insert into data select '005','e','002',0
insert into data select '006','f','002',0
insert into data select '007','g','002',0
insert into data select '008','h','002',0
go
select * from data order by (case when haschild=1 then id else parent end),id
go
drop table data
/*
id name parent hasChild
---------- ---------- ---------- -----------
001 a 0 1
003 c 001 0
004 d 001 0
002 b 0 1
005 e 002 0
006 f 002 0
007 g 002 0
008 h 002 0
(8 行受影响)
*/
use City;
go
if OBJECT_ID(N'A',N'U') is not null drop table A
go
create table A
(
id nvarchar(10) not null,
name nvarchar(20) ,
parent nvarchar(20) ,
hasChild int
)
go
--插入K3_Produce_File_Manage测试数据
insert into A
select '001','a','0', 1 union all
select '002', 'b','0', 1 union all
select '003','c', '001', 0 union all
select '004', 'd', '001', 0 union all
select '005', 'e', '002', 0 union all
select '006', 'f', '002', 0 union all
select '007', 'g', '002', 0 union all
select '008',' h', '002', 0
go
--插入Pro_Power测试数据
with cte as
(
select id, name, parent, hasChild,ROW_NUMBER()over(order by getdate()) as RN from A where parent='0'
union all
select A.id, A.name, A.parent, A.hasChild,cte.RN*10+ROW_NUMBER()over(order by getdate()) as RN from A inner join cte
on A.parent=cte.id
)
select id, name, parent, hasChild from cte order by ltrim(RN)
drop table A
/*
(8 行受影响)
id name parent hasChild
---------- -------------------- -------------------- -----------
001 a 0 1
003 c 001 0
004 d 001 0
002 b 0 1
005 e 002 0
006 f 002 0
007 g 002 0
008 h 002 0
(8 行受影响)
*/