~~~~~~~~~~~~~~大力请进~~~~~~~~~~~~~

Question

declare @TB_Category table (id int,ParentID int,name varchar(20))insert @TB_Category values(1,0,'Level1')
insert @TB_Category values(2,   1 ,      'Level2A')
insert @TB_Category values(3,   1  ,     'Level2B')
insert @TB_Category values(4,   2   ,    'Level3A')
insert @TB_Category values(5,   3    ,   'Level3B')
insert @TB_Category values(6,3      , 'Level3C')
declare @TB_Fee table (ID int,CategoryID int,Fee int)
insert @tb_fee values(1,   1       , 1)
insert @tb_fee values(2,   5       , 2)
insert @tb_fee values(3,   3       , 2)
insert @tb_fee values(4,   6       , 3)
insert @tb_fee values(5,   3       , 5)
insert @tb_fee values(6,   4  , 6)
insert @tb_fee values(7,   5       , 3)
insert @tb_fee values(8,   4       , 4)
insert @tb_fee values(9,   5       , 2)
insert @tb_fee values(10,   6       , 4)
insert @tb_fee values(11,   3       , 1)
insert @tb_fee values(12,   4       , 6)
insert @tb_fee values(13,   4       , 3)
insert @tb_fee values(14,   2       , 4)
insert @tb_fee values(15,   1       , 5)
insert @tb_fee values(16,   6       , 5)
insert @tb_fee values(17,   6       , 9)
declare @参数 varchar(100)
set @参数='Level1'declare @tmp1 table (id int,ParentID int,name varchar(20),层次 varchar(1000),层次2 varchar(1000))
insert @tmp1 (id,ParentID,name,层次) select *,cast(id as varchar(10))+',' from @TB_Category where name=@参数
while @@rowcount>0
  insert @tmp1 (id,ParentID,name,层次) select a.*,b.层次+cast(a.id as varchar(10))+',' from  @TB_Category a,@tmp1 b where a.ParentID=b.ID and a.ID not in (select ID from @tmp1)
declare @a table (id int not null)
declare @b varchar(8000)
select * from @tmp1
insert @a select top 1 id from @tmp1 where id not in (select id from @a) order by id
while @@rowcount>0
begin
  set @b=''
  update @tmp1 set @b=@b+cast(id as varchar(10))+',' from @tmp1 a where exists (select 1 from @tmp1 b where ','+a.层次+',' like '%,'+cast(b.id as varchar(10))+',%' and id=(select max(id) from @a))
  update @tmp1 set 层次2=@b where id=(select max(id) from @a)
  insert @a select top 1 id from @tmp1 where id not in (select id from @a) order by id
endselect * from @tmp1

Answer 1

declare @TB_Category table (id int,ParentID int,name varchar(20))insert @TB_Category values(1,0,'Level1')
insert @TB_Category values(2,   1 ,      'Level2A')
insert @TB_Category values(3,   1  ,     'Level2B')
insert @TB_Category values(4,   2   ,    'Level3A')
insert @TB_Category values(5,   3    ,   'Level3B')
insert @TB_Category values(6,3      , 'Level3C')
declare @TB_Fee table (ID int,CategoryID int,Fee int)
insert @tb_fee values(1,   1       , 1)
insert @tb_fee values(2,   5       , 2)
insert @tb_fee values(3,   3       , 2)
insert @tb_fee values(4,   6       , 3)
insert @tb_fee values(5,   3       , 5)
insert @tb_fee values(6,   4  , 6)
insert @tb_fee values(7,   5       , 3)
insert @tb_fee values(8,   4       , 4)
insert @tb_fee values(9,   5       , 2)
insert @tb_fee values(10,   6       , 4)
insert @tb_fee values(11,   3       , 1)
insert @tb_fee values(12,   4       , 6)
insert @tb_fee values(13,   4       , 3)
insert @tb_fee values(14,   2       , 4)
insert @tb_fee values(15,   1       , 5)
insert @tb_fee values(16,   6       , 5)
insert @tb_fee values(17,   6       , 9)
declare @参数 varchar(100)
set @参数='Level1'declare @tmp1 table (id int,ParentID int,name varchar(20),层次 varchar(1000),层次2 varchar(1000))
insert @tmp1 (id,ParentID,name,层次) select *,cast(id as varchar(10))+',' from @TB_Category where name=@参数
while @@rowcount>0
  insert @tmp1 (id,ParentID,name,层次) select a.*,b.层次+cast(a.id as varchar(10))+',' from  @TB_Category a,@tmp1 b where a.ParentID=b.ID and a.ID not in (select ID from @tmp1)
declare @a table (id int not null)
declare @b varchar(8000)
insert @a select top 1 id from @tmp1 where id not in (select id from @a) order by id
while @@rowcount>0
begin
  set @b=''
  update @tmp1 set @b=@b+cast(id as varchar(10))+',' from @tmp1 a where exists (select 1 from @tmp1 b where ','+a.层次+',' like '%,'+cast(b.id as varchar(10))+',%' and id=(select max(id) from @a))
  update @tmp1 set 层次2=@b where id=(select max(id) from @a)
  insert @a select top 1 id from @tmp1 where id not in (select id from @a) order by id
endselect * from @tmp1
/*
id          ParentID    name                 层次                                                                                                                                                                                                                                                               层次2                                                                                                                                                                                                                                                              
----------- ----------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1           0           Level1               1,                                                                                                                                                                                                                                                               1,2,3,4,5,6,
2           1           Level2A              1,2,                                                                                                                                                                                                                                                             2,4,
3           1           Level2B              1,3,                                                                                                                                                                                                                                                             3,5,6,
4           2           Level3A              1,2,4,                                                                                                                                                                                                                                                           4,
5           3           Level3B              1,3,5,                                                                                                                                                                                                                                                           5,
6           3           Level3C              1,3,6,                                                                                                                                                                                                                                                           6,（所影响的行数为 6 行）
*/

Answer 2

谢谢大力！
还有个问题我有两个字段
自增ID,时间如何按照时间DESC,ID ASC排序？
是不是要分组啊？

Answer 3

直接order by 时间 desc,id asc?
你加在那?

Answer 4

order by 时间 desc, id

Answer 5

declare @TB_Category table (id int,ParentID int,name varchar(20))insert @TB_Category values(1,0,'Level1')
insert @TB_Category values(2,   1 ,      'Level2A')
insert @TB_Category values(3,   1  ,     'Level2B')
insert @TB_Category values(4,   2   ,    'Level3A')
insert @TB_Category values(5,   3    ,   'Level3B')
insert @TB_Category values(6,3      , 'Level3C')
declare @TB_Fee table (ID int,CategoryID int,Fee int)
insert @tb_fee values(1,   1       , 1)
insert @tb_fee values(2,   5       , 2)
insert @tb_fee values(3,   3       , 2)
insert @tb_fee values(4,   6       , 3)
insert @tb_fee values(5,   3       , 5)
insert @tb_fee values(6,   4  , 6)
insert @tb_fee values(7,   5       , 3)
insert @tb_fee values(8,   4       , 4)
insert @tb_fee values(9,   5       , 2)
insert @tb_fee values(10,   6       , 4)
insert @tb_fee values(11,   3       , 1)
insert @tb_fee values(12,   4       , 6)
insert @tb_fee values(13,   4       , 3)
insert @tb_fee values(14,   2       , 4)
insert @tb_fee values(15,   1       , 5)
insert @tb_fee values(16,   6       , 5)
insert @tb_fee values(17,   6       , 9)
declare @参数 varchar(100)
set @参数='Level1'declare @tmp1 table (rowid int identity(1,1),时间 datetime,id int,ParentID int,name varchar(20),层次 varchar(20),层次2 varchar(1000))
insert @tmp1 (id,ParentID,name,层次) select *,cast(id as varchar(10))+',' from @TB_Category where name=@参数
while @@rowcount>0
  insert @tmp1 (id,ParentID,name,层次) select a.*,b.层次+cast(a.id as varchar(10))+',' from  @TB_Category a,@tmp1 b where a.ParentID=b.ID and a.ID not in (select ID from @tmp1)
declare @a table (id int not null)
declare @b varchar(8000)
insert @a select top 1 id from @tmp1 where id not in (select id from @a) order by id
while @@rowcount>0
begin
  set @b=''
  update @tmp1 set @b=@b+cast(id as varchar(10))+',' from @tmp1 a where exists (select 1 from @tmp1 b where ','+a.层次+',' like '%,'+cast(b.id as varchar(10))+',%' and id=(select max(id) from @a))
  update @tmp1 set 层次2=@b where id=(select max(id) from @a)
  insert @a select top 1 id from @tmp1 where id not in (select id from @a) order by id
endselect * from @tmp1 order by 时间 desc,rowid
/*rowid       时间                                                     id          ParentID    name                 层次                   层次2                                                                                                                                                                                                                                                              
----------- ------------------------------------------------------ ----------- ----------- -------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1           NULL                                                   1           0           Level1               1,                   1,2,3,4,5,6,
2           NULL                                                   2           1           Level2A              1,2,                 2,4,
3           NULL                                                   3           1           Level2B              1,3,                 3,5,6,
4           NULL                                                   4           2           Level3A              1,2,4,               4,
5           NULL                                                   5           3           Level3B              1,3,5,               5,
6           NULL                                                   6           3           Level3C              1,3,6,               6,（所影响的行数为 6 行）
*/