Question

sql如何实行多条件分类汇总

item receiptno customer quantity amount
ref23001 C01 DFDF 20 200
ref23002 C01 FGFG 10 100
ref23001 CO2 DFDF 5 50
ref23002 C02 FGFG 8 80
.
.
.
我如何在SQL中能够先汇总receiptno(单据类型C01表示销售，C02表示退货）再汇总customer(客户）item(销售的产品）quantity表示数据 amount表示金额，最后用汇总后的C01-C02(销售－退货)

Answer 1

select item_no,sum(case receiptno when 'C01' then quantity else -1 * quantity end) as quantity ,sum(case receiptno when 'C01' then amount
else -1 * amount end ) amount
from table
group by item_no