# 请一个select查询语句,该如何解决

www.myexceptions.net  网友分享于：2013-03-06  浏览：2次

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

------解决方案--------------------
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 tb
group by Product
------解决方案--------------------不好意思，刚才的反了,参考以下SQL codeselect Product, sum((case when BS='B' then -1 else 1 end) *Quantity)
from tb
group by Product
------解决方案--------------------/*先分组*/SELECT ProductWHERE 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```