查询问题请教

hollyapple11 2018-06-12 11:19:48

表如下：

  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

想要得到以下较果：

订单号产品编号产品名称规格单位车间出厂日期数量
'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
小计 940
'201702','B3003','线圈','B0-E','组','P31','20170220',350
'201703','B3003','线圈','B0-E','组','P31','20170320',100
'201703','B3003','线圈','B0-E','组','P31','20170320',210
小计 660
'201705','C2002','外壳','C0-NN','套','H01','20170505',300
'201705','C2002','外壳','C0-NN','套','H01','20170506',300
小计 600
总计 2200

请问应该怎么写？

...全文

183 点赞收藏 1

写回复

1 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

听雨停了 2018-06-12


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 行受影响)

要搞得一摸一样，需要再调一下。大概的逻辑就这样吧

回复赞