日期:2014-05-18 浏览次数:20727 次
declare @t table (ID varchar(3),clsID varchar(4),plu varchar(8),sale int,time varchar(10),lsh varchar(8)) insert into @t select '004','一级',1001,100,'2011-01-01','00001' union all select '001','一级',1002,50,'2011-01-02','00003' union all select '002','二级',1001,100,'2011-02-01','00007' union all select '002','二级',1003,10,'2011-03-01','00009' union all select '001','一级',1003,10,'2011-04-01','00004' union all select '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 all select '','小计',clsID,'',sum(sale),'','' from @t group by clsID union all select '','合计','','',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 00007 00005 002 二级 1003 10 2011-03-01 00009 00006 003 二级 1002 50 2011-05-01 00011 小计 二级 160 00003 001 一级 1003 10 2011-04-01 00004 00001 004 一级 1001 100 2011-01-01 00001 00002 001 一级 1002 50 2011-01-02 00003 小计 一级 160 合计 320 */
select xh=identity(int,1,1),* into #tb from tb order by clsid select right('0000'+rtrim(xh),4) as xh,* from #tb select '','','小计',clsID,'',sum(sale),'','' from @t group by clsID union all select '','','合计','','',sum(sale),'','' from @t order by (case when clsID='合计' then 1 else 0 end), clsid,( case xh when '' then 2 else 1 end),xh drop table #tb
------解决方案--------------------
这个适用于不超过十级的情况!
declare @t table (ID varchar(3),clsID varchar(4),plu varchar(8),sale int,time varchar(10),lsh varchar(8)) insert into @t select '004','一级',1001,100,'2011-01-01','00001' union all select '001','一级',1002,50,'2011-01-02','00003' union all select '002','二级',1001,100,'2011-02-01','00007' union all select '002','二级',1003,10,'2011-03-01','00009' union all select '001','一级',1003,10,'2011-04-01','00004' union all select '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 all select '','小计',clsID,'',sum(sale),'','' from @t group by clsID union all select '','合计','','',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 00001 00002 001 一级 1002 50 2011-01-02 00003 00003 001 一级 1003 10 2011-04-01 00004 小计 一级 160 00004 002 二级 1001 100 2011-02-01 00007 00005 002 二级 1003 10 2011-03-01 00009 00006 003 二级 1002 50 2011-05-01 00011 小计 二级 160 合计 320 (9 行受影响)