请一个select查询语句

[benbebnmao]

现有商品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？
多谢指教！

[dawugui]

select Product ,
sum(case when BS = 'B' then Quantity else -Quantity end)
from tb
group by Product

[dawugui]

create table tb(Product varchar(10),BS varchar(10), Quantity int)
insert into tb values('P1', 'B', 100)
insert into tb values('P1', 'B', 200)
insert into tb values('P1', 'S', 500)
insert into tb values('p2', 'B', 400)
insert into tb values('p2', 'S', 900)
insert into tb values('P2', 'S', 100)
go

select Product ,Quantity = 
       sum(case when BS = 'B' then -Quantity else Quantity end)
from tb
group by Product
/*
Product    Quantity    
---------- ----------- 
P1         200
p2         600

（所影响的行数为 2 行）
*/

select Product ,Quantity = 
       sum(case when BS = 'B' then -Quantity when bs = 's' then Quantity end)
from tb
group by Product
/*
Product    Quantity    
---------- ----------- 
P1         200
p2         600

（所影响的行数为 2 行）
*/

drop table tb

[duanzhi1984]

select Product, sum((case when BS='B' then 1 else -1 end) *Quantity)
from tb
group by Product

[duanzhi1984]

不好意思，刚才的反了,参考以下

select Product, sum((case when BS='B' then -1 else 1 end) *Quantity)
from tb
group by Product 

[Rotel-刘志东]

select Product ,sum(case when BS = 'B' then Quantity else -Quantity end)
from tb
group by Product

[山书生]

~.`灵活，学习:)

[cxmcxm]

select Product,sum(case BS when 'S' then Quantity else -Quantity end) as sl
from 表 group by product

[ibm8808]

/*先分组*/
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

[lantian_019]

if OBJECT_ID('tab') is not null
drop table tab;
go
create table tab(name char(5),kind char(2), sale int);
go
insert into tab
select 'P1', 'B', 100 union all
select 'P1', 'B',200 union all
select 'P1', 'S', 500 union all
select 'p2', 'B', 400 union all
select 'p2', 'S', 900 union all
select 'P2', 'S',100
select name,sale=sum(case when kind = 'B' then -sale else sale end ) from tab
group by name

[siegebaoniu]

咋一看以为还需要列出计算公式呢，不要的话挺简单的。

select Product ,
sum(case when BS = 'B' then Quantity else -Quantity end) as Quantity
from tb group by Product

[jamk]

select product,sum(case when BS='B' then Quantity else -Quantity end) as Quantity
from TB group by product

[zhangbiaoandbill]

他的本意是不是有三列啊？三列的话，第三列也没什么意义啊