27,579
社区成员
发帖
与我相关
我的任务
分享
ID clsID plu sale time
001 一级 1001 100 2011-01-01
001 一级 1002 50 2011-01-02
002 二级 1001 100 2011-02-01
002 二级 1003 10 2011-03-01
001 一级 1003 10 2011-04-01
003 二级 1002 50 2011-05-01
XH ID clsID plu sale time
01 001 一级 1001 100 2011-01-01
02 001 一级 1002 50 2011-01-02
03 001 一级 1003 10 2011-04-01
小计 一级 160.00
04 002 二级 1001 100 2011-02-01
05 002 二级 1003 10 2011-03-01
06 003 二级 1002 50 2011-05-01
小计 二级 160.00
declare @t table (ID varchar(3),clsID varchar(4),plu varchar(8),sale int,time varchar(10))
insert into @t
select '001','一级',1001,100,'2011-01-01' union all
select '001','一级',1002,50,'2011-01-02' union all
select '002','二级',1001,100,'2011-02-01' union all
select '002','二级',1003,10,'2011-03-01' union all
select '001','一级',1003,10,'2011-04-01' union all
select '003','二级',1002,50,'2011-05-01'
select * from @t union all
select '小计',clsID,'',sum(sale),null from @t
group by clsID
order by clsid desc,ID
/*
ID clsID plu sale time
---- ----- -------- ----------- ----------
001 一级 1001 100 2011-01-01
001 一级 1002 50 2011-01-02
001 一级 1003 10 2011-04-01
小计 一级 160 NULL
002 二级 1001 100 2011-02-01
002 二级 1003 10 2011-03-01
003 二级 1002 50 2011-05-01
小计 二级 160 NULL
*/
--maco_wang:我只对你说 强!!!
declare @t table (ID varchar(3),clsID varchar(4),plu varchar(8),sale int,time varchar(10),lsh varchar(8))
insert into @t
select '001','一级',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 right('00000'+ltrim((select count(1) from @t where lsh<=a.lsh)),5) as xh,* from @t a union all
select '','小计',clsID,'',sum(sale),null,null from @t
group by clsID
order by clsid desc,ID
/*
xh ID clsID plu sale time lsh
---------- ---- ----- -------- ----------- ---------- --------
00001 001 一级 1001 100 2011-01-01 00001
00002 001 一级 1002 50 2011-01-02 00003
00003 001 一级 1003 10 2011-04-01 00004
小计 一级 160 NULL NULL
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 NULL NULL
*/
declare @t table (ID varchar(3),clsID varchar(4),plu varchar(8),sale int,time varchar(10))
insert into @t
select '001','一级',1001,100,'2011-01-01' union all
select '001','一级',1002,50,'2011-01-02' union all
select '002','二级',1001,100,'2011-02-01' union all
select '002','二级',1003,10,'2011-03-01' union all
select '001','一级',1003,10,'2011-04-01' union all
select '003','二级',1002,50,'2011-05-01'
select xh=ROW_NUMBER() over(order by ID),* from @t union all
select null,'小计',clsID,'',sum(sale),null from @t
group by clsID
order by clsid desc,ID
/*
(6 行受影响)
xh ID clsID plu sale time
-------------------- ---- ----- -------- ----------- ----------
1 001 一级 1001 100 2011-01-01
2 001 一级 1002 50 2011-01-02
3 001 一级 1003 10 2011-04-01
NULL 小计 一级 160 NULL
4 002 二级 1001 100 2011-02-01
5 002 二级 1003 10 2011-03-01
6 003 二级 1002 50 2011-05-01
NULL 小计 二级 160 NULL
(8 行受影响)