一个表,记录一个产品的出入库情况
如下
id name num isout
A aa 20 1
A aa 10 1
A aa 10 0
B bb 30 1
C cc 10 0
C cc 10 0
现在要得到这样的结果
id name out in
A aa 30 10
B bb 30 0
C cc 0 20
也就是根据isout来统计是out还是in,然后加起来
------解决方案--------------------
试试这个：
select id,name,
sum(case when isout = 1 then num else 0 end) out,
sum(case when isout = 0 then num else 0 end) int
from 表
group by id,name
------解决方案--------------------
create table xe
(id varchar(10),name varchar(10),num int,isout int)
insert into xe
select 'A','aa',20,1 union all
select 'A','aa',10,1 union all
select 'A','aa',10,0 union all
select 'B','bb',30,1 union all
select 'C','cc',10,0 union all
select 'C','cc',10,0
select id,name,
sum(case isout when 1 then num else 0 end) 'out',
sum(case isout when 1 then 0 else num end) 'int'
from xe
group by id,name
/*
id name out int
---------- ---------- ----------- -----------
A aa 30 10
B bb 30 0
C cc 0 20
(3 row(s) affected)
*/
------解决方案--------------------
练习
select id,name,
sum(case when isout = 1 then Num else 0 end) as 'out',
sum(case when isout = 0 then Num else 0 end) as 'in'
from 表名
group by id,name