22,300
社区成员




create table #test (订单号 varchar(10),产品编号 varchar(10),产品名称 varchar(20),规格 varchar(20),单位 varchar(10),车间 varchar(10),出厂日期 varchar(20),数量 int,)
insert into #test
select '201701','A1001','电芯','20-AC','个','H01','20170115',200 union all
select '201701','A1001','电芯','20-AC','个','H01','20170115',200 union all
select '201702','A1001','电芯','20-AC','个','H01','20170201',150 union all
select '201702','B3003','线圈','B0-E','组','P31','20170220',350 union all
select '201703','A1001','电芯','20-AC','个','H01','20170315',150 union all
select '201703','B3003','线圈','B0-E','组','P31','20170320',100 union all
select '201703','B3003','线圈','B0-E','组','P31','20170320',210 union all
select '201704','A1001','电芯','20-AC','个','H01','20170415',150 union all
select '201705','C2002','外壳','C0-NN','套','H01','20170505',300 union all
select '201705','A1001','电芯','20-AC','个','H01','20170504',90 union all
select '201705','C2002','外壳','C0-NN','套','H01','20170506',300
select * from #test
select * from #test
UNION all
SELECT '小计' as 订单号,产品编号,NULL,null,NULL,null,NULL,sum(数量) from #test
GROUP BY 产品编号
UNION all
SELECT '总计' as 订单号,'z',NULL,null,NULL,null,NULL,sum(数量) from #test
ORDER BY 产品编号,订单号
订单号 产品编号 产品名称 规格 单位 车间 出厂日期 数量
---------- ---------- -------------------- -------------------- ---------- ---------- -------------------- -----------
201701 A1001 电芯 20-AC 个 H01 20170115 200
201701 A1001 电芯 20-AC 个 H01 20170115 200
201702 A1001 电芯 20-AC 个 H01 20170201 150
201703 A1001 电芯 20-AC 个 H01 20170315 150
201704 A1001 电芯 20-AC 个 H01 20170415 150
201705 A1001 电芯 20-AC 个 H01 20170504 90
小计 A1001 NULL NULL NULL NULL NULL 940
201702 B3003 线圈 B0-E 组 P31 20170220 350
201703 B3003 线圈 B0-E 组 P31 20170320 100
201703 B3003 线圈 B0-E 组 P31 20170320 210
小计 B3003 NULL NULL NULL NULL NULL 660
201705 C2002 外壳 C0-NN 套 H01 20170506 300
201705 C2002 外壳 C0-NN 套 H01 20170505 300
小计 C2002 NULL NULL NULL NULL NULL 600
总计 z NULL NULL NULL NULL NULL 2200
(15 行受影响)
要搞得一摸一样,需要再调一下。大概的逻辑就这样吧