SQL 语句 求和

[lakibo]

Table Products : TID , cost
Table Parts : PID , TID , quantity

已知 PID , 从 表 Parts 中获取 list of TID ，求和 cost

Sum = Sum(cost * quantity)

谢谢了

[caixia615]

SELECT pid,sum(products.cost*parts.quantity) from products,parts where products.tid=parts.tid

[lakibo]

不行阿。

[CaiNiaoWuZui]

select parts.pid,sum((isNull(products.cost),0))*parts.quantity) from parts left join products on parts.tid=products.tid group by parts.pid

[lakibo]

理论上

select sum(products.cost * parts.quantity) from products where TID = { select TID from parts where PID = 'somepid' }

有许多 TID

[mschen]

/* mysql里边应该使用IFNULL */

select parts.pid,
sum(IFNull(products.cost,0)*parts.quantity)
from parts left join products
on parts.tid=products.tid
group by parts.pid
having parts.pid='somepid'

[懒得去死]

select parts.pid, sum(IFNull(products.cost,0)*parts.quantity) from parts left join products on parts.tid=products.tid group by parts.pid having parts.pid='somepid'