sql 汇总表

来源：百度知道 编辑：UC知道 时间：2024/07/02 00:57:27
请问各位高手,有如下表
code | name | month | inquantity | inprice | outquqntity |outprice
001 |a | 0 | 10 | 12 | |
002 |b | 0 | 10 | 20 | |
003 |c | 0 | 5 | 10 | |
001 |a | 1 | 20 | 40 | |
001 |a | 1 | | | 3 | 3.6
002 |b | 1 | 2 | 4 | |
003 |c | 2 | | | 2 | 4
004 |d | 2 | 14 | 28 | |
004 |d | 3 | | | 3 | 6
其中month为月份,0月份即 是期初
要求生成如下表
code |qcqua(数量)|qcpr(金额)|nquantity(入数)|npr(金额)|outqu(出数)|price(金额| kcqu(存数)| kcpr(金额)
001 | 10 | 12 | 20 | 40 | 3 | 3.6 | 27 | 56.4
002 | 10 | 20 | 2 | 4 | | | 12 | 24
0

终于把问题搞定！你的问题有一处错误，code为001期末的 kcpr(金额) 应为48.4，而不是56.4。SQL语句如下（假定表名为TEST,已在SQL2000上运行通过）：
select code,sum(case when month = 0 and inquantity is not null then inquantity else 0 end) as "qcqua(数量)",sum(case when month=0 and inprice is not null then inprice else 0 end ) as "qcpr(金额)",sum(case when month <>0 and inquantity is not null then inquantity else 0 end) as "nquantity(入数)",sum(case when month<>0 and inprice is not null then inprice else 0 end ) as "npr(金额)",sum(case when outquqntity is not null then outquqntity else 0 end) as "outqu(出数)",sum(case when outprice is not null then outprice else 0 end) as "price(金额)",sum(case when month = 0 and inquantity is not null then inquantity else 0 end)+sum(case when month <>0 and inquantity is not null then inquantity else 0 end)-sum(case when outquqntity is not null then outquqntity else 0 end) as "kcqu(存数)",sum(case when month=0 and inprice is n