现有商品p1,p2,有一表记录一个月来的买入和卖出的数量。
Product BS Quantity
P1 B 100
P1 B 200
P1 S 500
p2 B 400
p2 S 900
P2 S 100
B为进货，S为卖出。现在想统计每个产品这个月来的净卖出量。希望得到这样的结果：
P1 200 //500-200-100
p2 600 //900+100-400
该怎么写SQL语句啊？select中可以用if吗?或者case？
多谢指教！
------解决方案--------------------
select Product ,
sum(case when BS = 'B' then Quantity else -Quantity end)
from tb
group by Product
------解决方案--------------------
- SQL code
select Product, sum((case when BS='B' then 1 else -1 end) *Quantity)from tbgroup by Product
------解决方案--------------------
不好意思，刚才的反了,参考以下
- SQL code
select Product, sum((case when BS='B' then -1 else 1 end) *Quantity)from tbgroup by Product
------解决方案--------------------
/*先分组*/
SELECT Product
WHERE Product=ProductRoot.Product) AS MyT FROM [tb] AS ProductRoot
GROUP BY Product
/*设A 为一个产品的进入货数总数
设B 为一个产品的进入货数总数
设C C=B-A
*/
/*A*/
SELECT SUM(Quantity) FROM [tb] WHERE Product=ProductRoot.Product AND BS='B'
/*B*/
SELECT SUM(Quantity) FROM [tb] WHERE Product=ProductRoot.Product AND BS='S'
结果:
/*代入A到分组中*/
SELECT Product, (SELECT SUM(Quantity) FROM [tb] WHERE Product=ProductRoot.Product AND BS='B')
FROM [tb] AS ProductRoot
GROUP BY Product
最终SQL语句
/*代入B到分组中结合A*/
SELECT Product, (SELECT SUM(Quantity) FROM [tb] WHERE Product=ProductRoot.Product AND BS='S')-(SELECT SUM(Quantity) FROM [tb] WHERE Product=ProductRoot.Product AND BS='B')
FROM [tb] AS ProductRoot
GROUP BY Product