分组小计 合计，自动增加排序号，按叶子和乌龟的做法改的 上个贴没有回答， sql2000

分组小计 合计，自动增加排序号，按叶子和乌龟的做法改的 上个贴没有回答，高手请进 sql2000

SQL code

declare @t table (ID varchar(3),clsID varchar(4),plu varchar(8),sale int,time varchar(10),lsh varchar(8))insert into @tselect '004','一级',1001,100,'2011-01-01','00001' union allselect '001','一级',1002,50,'2011-01-02','00003' union allselect '002','二级',1001,100,'2011-02-01','00007' union allselect '002','二级',1003,10,'2011-03-01','00009' union allselect '001','一级',1003,10,'2011-04-01','00004' union allselect '003','二级',1002,50,'2011-05-01','00011'select * from (select right('00000'+ltrim((select count(1) from @t where lsh<=a.lsh)),5) as xh,* from @t a union allselect '','小计',clsID,'',sum(sale),'','' from @tgroup by clsID union allselect '','合计','','',sum(sale),'','' from @t)t order by case clsID when '' then 2 else 1 end,clsid,case id when '小计' then 2 else 1 end/*xh         ID   clsID plu      sale        time       lsh---------- ---- ----- -------- ----------- ---------- --------00004    002    二级    1001    100    2011-02-01    0000700005    002    二级    1003    10    2011-03-01    0000900006    003    二级    1002    50    2011-05-01    00011    小计    二级        160        00003    001    一级    1003    10    2011-04-01    0000400001    004    一级    1001    100    2011-01-01    0000100002    001    一级    1002    50    2011-01-02    00003    小计    一级        160            合计            320        */

SQL code

select xh=identity(int,1,1),* into #tb from tb order by clsidselect right('0000'+rtrim(xh),4) as xh,* from #tbselect '','','小计',clsID,'',sum(sale),'','' from @tgroup by clsID union allselect '','','合计','','',sum(sale),'','' from @torder by (case when clsID='合计' then 1 else 0 end),         clsid,( case xh when '' then 2 else 1 end),xhdrop table #tb
[解决办法]
这个适用于不超过十级的情况！

SQL code
declare @t table (ID varchar(3),clsID varchar(4),plu varchar(8),sale int,time varchar(10),lsh varchar(8))insert into @tselect '004','一级',1001,100,'2011-01-01','00001' union allselect '001','一级',1002,50,'2011-01-02','00003' union allselect '002','二级',1001,100,'2011-02-01','00007' union allselect '002','二级',1003,10,'2011-03-01','00009' union allselect '001','一级',1003,10,'2011-04-01','00004' union allselect '003','二级',1002,50,'2011-05-01','00011'select * from (select right('00000'+ltrim((select count(1) from @t where lsh<=a.lsh)),5) as xh,* from @t a union allselect '','小计',clsID,'',sum(sale),'','' from @tgroup by clsID union allselect '','合计','','',sum(sale),'','' from @t)t order by case clsID when '' then 2 else 1 end,    charindex(left(clsid,1),'一二三四五六七八九十'),    case id when '小计' then 2 else 1 end,xh/***********************xh         ID   clsID plu      sale        time       lsh---------- ---- ----- -------- ----------- ---------- --------00001      004  一级    1001     100         2011-01-01 0000100002      001  一级    1002     50          2011-01-02 0000300003      001  一级    1003     10          2011-04-01 00004           小计   一级             160                    00004      002  二级    1001     100         2011-02-01 0000700005      002  二级    1003     10          2011-03-01 0000900006      003  二级    1002     50          2011-05-01 00011           小计   二级             160                               合计                  320                    (9 行受影响)