SQL 写进出的差额

[zhmjppp]

有table如下,写出了主要字段

pid(商品id)  num(数量)   state(状态,0为买进,1为卖出)
1001         100         0
1002         200         0
1003         150         0
1002         1           1
1003         2           1
1001         3           1

求剩余的数量

[--小F--]

select
  a.pid,isnull(a.num,0)-isnull(b.num,0) as num
from
  (select pid,sum(num) as num from tb where state=0 group by pid)a,
  (select pid,sum(num) as num from tb where state=0 group by pid)b
where
   a.pid=b.pid

[--小F--]

select
  a.pid,isnull(a.num,0)-isnull(b.num,0) as num
from
  (select pid,sum(num) as num from tb where state=0 group by pid)a,
  (select pid,sum(num) as num from tb where state=1 group by pid)b
where
   a.pid=b.pid

[ff284996867]

select sum(case when state=0 then num else (num*-1) end)
from tb

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-10-09 11:16:10
-- Verstion:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([pid] int,[num] int,[state] int)
insert [tb]
select 1001,100,0 union all
select 1002,200,0 union all
select 1003,150,0 union all
select 1002,1,1 union all
select 1003,2,1 union all
select 1001,3,1
--------------开始查询--------------------------
--方法1
select
  a.pid,isnull(a.num,0)-isnull(b.num,0) as num
from
  (select pid,sum(num) as num from tb where state=0 group by pid)a,
  (select pid,sum(num) as num from tb where state=1 group by pid)b
where
   a.pid=b.pid
   
---方法2
select 
   pid,SUM(num) as num
from
   (select pid,num from tb where state=0
    union all
    select pid,-num from tb where state=1)t
group by
   pid 



----------------结果----------------------------
/* pid         num
----------- -----------
1001        97
1002        199
1003        148

*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-10-09 11:16:10
-- Verstion:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([pid] int,[num] int,[state] int)
insert [tb]
select 1001,100,0 union all
select 1002,200,0 union all
select 1003,150,0 union all
select 1002,1,1 union all
select 1003,2,1 union all
select 1001,3,1
--------------开始查询--------------------------
--方法1
select
  a.pid,isnull(a.num,0)-isnull(b.num,0) as num
from
  (select pid,sum(num) as num from tb where state=0 group by pid)a,
  (select pid,sum(num) as num from tb where state=1 group by pid)b
where
   a.pid=b.pid
   
---方法2
select 
   pid,SUM(num) as num
from
   (select pid,num from tb where state=0
    union all
    select pid,-num from tb where state=1)t
group by
   pid 


---方法3

select
   pid,SUM(case state when 0 then num else -num end) AS NUM
 from
   tb
group by
   pid 
----------------结果----------------------------
/* pid         num
----------- -----------
1001        97
1002        199
1003        148

*/

[dawugui]

select pid , sum(case when state = 0 then num else -num end) from tb group by pid

[dawugui]

create table tb(pid int,num int,state int)
insert into tb values(1001    ,     100     ,    0)
insert into tb values(1002    ,     200     ,    0)
insert into tb values(1003    ,     150     ,    0)
insert into tb values(1002    ,     1       ,    1)
insert into tb values(1003    ,     2       ,    1)
insert into tb values(1001    ,     3       ,    1)
go

select pid , sum(case when state = 0 then num else -num end) from tb group by pid

drop table tb



/*
pid                     
----------- ----------- 
1001        97
1002        199
1003        148

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

[wangxiaofeiwuqiao]

SELECT pid,
SUM(CASE WHEN STATE='0' THEN num ELSE 0 end) AS '买进',
SUM(CASE WHEN STATE='1' THEN num ELSE 0 end) AS '买出',
SUM(CASE WHEN STATE='0' THEN num ELSE 0 end)-SUM(CASE WHEN STATE='1' THEN num ELSE 0 end) AS '差额'
FROM tb GROUP BY pid

[feihongluori]

use students3;
go 
if object_id('tb3')is not null
drop table tb3;
go
create table tb3
(
pid int,
num int,
state int
)
insert into tb3
select 1001,100,0 union all
select 1002,200,0 union all
select 1003,150,0 union all
select 1002,1,1 union all
select 1003,2,1 union all
select 1001,3,1
select pid as '商品ID',num as '商品数量',state as '售况',(num-state)as '剩余数量'from tb3
/*(6 行受影响)
商品ID        商品数量        售况          剩余数量
----------- ----------- ----------- -----------
1001        100         0           100
1002        200         0           200
1003        150         0           150
1002        1           1           0
1003        2           1           1
1001        3           1           2

(6 行受影响)

*/