100分在线等这个不是很复杂的sql语句
T表：
TID Quantity ProductID Type
1 2 12 1
2 6 12 2
5 3 10 2
7 8 10 1
其中Type=1表示卖,Type=2表示买,ProductID为商品ID，
现在要计算现有的每种商品有多少件，该怎么写这个SQL语句
[解决办法]
select
ProductID,sum(case Type when 1 then Quantity else -Quantity end) as Quantity
from
T表
group by
ProductID
[解决办法]
declare @T table(TID int,Quantity int,ProductID int,Type int)
insert into @T select 1,2,12,1
insert into @T select 2,6,12,2
insert into @T select 5,3,10,2
insert into @T select 7,8,10,1
select
ProductID,sum(case Type when 1 then Quantity else -Quantity end) as Quantity
from
@T
group by
ProductID
/*
ProductID Quantity
----------- -----------
10 5
12 -4
*/
[解决办法]
select productid,sum(case type when 1 then -cast(quantity as int) when 2 then quantity else end) as 数量 group by productid
[解决办法]
楼上正解。。。顶了
[解决办法]
借用楼上的数据
declare @T table(TID int,Quantity int,ProductID int,Type int)
insert into @T select 1,2,12,1
insert into @T select 2,6,12,2
insert into @T select 5,3,10,2
insert into @T select 7,8,10,1
select productid,sum(case type when 1 then -quantity when 2 then quantity end) as 数量 from @t group by productid
--------------------
product 数量
10-5
124
