恳求一条SQL代码,谢谢大家
单据表:chitty
chitty_id sup_id(供应商ID)
1 3
2 5
3 3
明细表:proitem
id chitty_id(单据ID) pro_id(产品ID) pro_amount(产品数量) pro_price(单价)
1 1 1 10 1.00
2 1 2 5 2.00
3 2 1 6 1.50
4 2 2 10 1.00
如何汇总得到(在存储过程中怎么写):
pro_id pro_amountsum(总数量) pro_pricesum(总金额)
1 16 9.00
2 15 20.00
[解决办法]
select pro_id,sum(pro_amount) pro_amountsum, sum(pro_amount*pro_price) pro_pricesum from proitem group by pro_id
[解决办法]
来晚了……
[解决办法]
没有group by 用sum当然不对
[解决办法]
/*
chitty_id sup_id(供应商ID)
1 3
2 5
3 3
明细表:proitem
id chitty_id(单据ID) pro_id(产品ID) pro_amount(产品数量) pro_price(单价)
1 1 1 10 1.00
2 1 2 5 2.00
3 2 1 6 1.50
4 2 2 10 1.00
*/
drop table vendor
drop table proitem
create table vendor(chitty_id int,sup_id int)
create table proitem(id int identity(1,1),chitty_id int,pro_id int,pro_amount int,pro_price decimal(18,3))
insert into vendor
select 1,3 union all
select 2,5 union all
select 3,3
insert into proitem(chitty_id,pro_id,pro_amount,pro_price)
select 1,1,10,1.0 union all
select 1,2,5,2.0 union all
select 2,1,6,1.50 union all
select 3,2,10,1.0
select
pro_id,
sum(pro_amount) as 'QtyAmount ',
sum(pro_amount*pro_price)as 'Amount '
from proitem
group by pro_id
