表chitty(单据表)
chitty_id cup_id(供应商ID)
1 1
2 3
3 1
表item(明细表)
id chitty_id(单据ID) chitty_sort(单据类型) pro_id pro_amount pro_price
1 1 1(表示采购入库) 2 10 20.00
2 2 1(表示采购入库) 3 15 5.00
3 2 2(表示采购退货) 3 20 5.00
4 3 1(表示采购入库) 3 20 10.00
如何汇总得到:
供应商ID 入库数量 入库金额 退货数量 退货金额
1 30 400.00 0 0.00
3 15 75.00 20 100.00
如何得到有多少个供应商
如:本例中有2个供应商.
谢谢大家
chitty_id cup_id(供应商ID)
1 1
2 3
3 1
表item(明细表)
id chitty_id(单据ID) chitty_sort(单据类型) pro_id pro_amount pro_price
1 1 1(表示采购入库) 2 10 20.00
2 2 1(表示采购入库) 3 15 5.00
3 2 2(表示采购退货) 3 20 5.00
4 3 1(表示采购入库) 3 20 10.00
如何汇总得到:
供应商ID 入库数量 入库金额 退货数量 退货金额
1 30 400.00 0 0.00
3 15 75.00 20 100.00
如何得到有多少个供应商
如:本例中有2个供应商.
谢谢大家
供应商ID=a.cup_id,
入库数量=sum(case b.chitty_sort when 1 then b.pro_amount else 0 end),
入库金额=sum(case b.chitty_sort when 1 then b.pro_amount*b.pro_price else 0 end),
退货数量=sum(case b.chitty_sort when 2 then b.pro_amount else 0 end),
退货金额=sum(case b.chitty_sort when 2 then b.pro_amount*b.pro_price else 0 end)
from
chitty a,item b
where
a.chitty_id=b.chitty_id
group by
a.cup_id
sum(case when chitty_sort=1 then pro_amount else 0 end) 入库数量,
sum(case when chitty_sort=1 then pro_amount*pro_price else 0 end) 入库金额,
sum(case when chitty_sort=2 then pro_amount else 0 end) 退货数量,
sum(case when chitty_sort=2 then pro_amount*pro_price else 0 end) 退货金额
from item a left join chitty b on a.chitty_id=b.chitty_id
group by b.cup_id
from (select distinct cup_id from item a,chitty b where a.chitty_id=b.chitty_id)aa
供应商ID=a.cup_id,
供应商名称=exec(select sup_name from crm_supplier)
为什么是错的.