请教一条Sql语句

[tinranqi]

我有一个表，表结构如下
sno type number
s1 IN 10
s1 IN 5
s1 OUT 2
s1 OUT 3
s1 OUT 3
s2...............(IN OUT)
s3...............(IN OUT)

我要实现
对于一个sno他IN的总数量和OUT的总数量如
sno sum_in_number sum_out_number
s1 15 8

请问sql语句怎么写？谢谢

[阿冷]

试验一下这样可以吗
select sno,
sum(decode(type,'IN',number,0) sum_in_number,
sum(decode(type,'OUT',number,0) sum_out_number
from T
group by sno

[yau11]

select sno,sum(case when type='IN' then number else 0 end) sum_in_number,sum(case when type='OUT' then number else 0 end) sum_out_number from table_name group by sno

[hupeng213]

[Quote=引用 1 楼 的回复:]
试验一下这样可以吗
select sno,
sum(decode(type,'IN',number,0) sum_in_number,
sum(decode(type,'OUT',number,0) sum_out_number
from T
group by sno
[/Quote]

+1

[q806294478]

WITH T AS(
SELECT 's1' SNO, 'IN' TYPE, 10 "NUMBER"  FROM DUAL
UNION
SELECT 's1' , 'IN',5 FROM DUAL
UNION
SELECT 's1', 'OUT', 2 FROM DUAL
UNION 
SELECT 's2', 'OUT',3 FROM DUAL
UNION ALL
SELECT 's2', 'OUT',3 FROM DUAL
 ) 
 SELECT SNO,
        SUM(DECODE(TYPE,'IN',"NUMBER")) AS sum_in_number ,
        SUM(DECODE(TYPE,'OUT',"NUMBER")) AS sum_out_number
 FROM T
 GROUP BY SNO;
结果：
SNO SUM_IN_NUMBER          SUM_OUT_NUMBER         
--- ---------------------- ---------------------- 
s1  15                     2                      
s2                         6    

[lithor]

如果楼主使用的是ORACLE11G，推荐使用下面方法：

select * from (
select tt.sno, tt.type, sum(tt.num) as quantity
  from test_tab tt
 group by tt.sno, tt.type
 ) t1 pivot(max(quantity) as sum_quantity for (type) in('OUT' AS O , 'IN' AS I))

关于PIVOT可以参见：http://blog.csdn.net/lithor/article/details/7730624

[Kooola的博客]

select distinct sno,(select sum(snum) from a where stype = 'in') sum_in_number,
(select sum(snum) from a where stype = 'out') sun_out_number from a ;

[yejihui9527]

WITH T AS(
SELECT 's1' SNO, 'IN' TYPE, 10 "NUMBER" FROM DUAL
UNION
SELECT 's1' , 'IN',5 FROM DUAL
UNION
SELECT 's1', 'OUT', 2 FROM DUAL
UNION
SELECT 's2', 'OUT',3 FROM DUAL
UNION ALL
SELECT 's2', 'OUT',3 FROM DUAL
)
SELECT SNO,
SUM(DECODE(TYPE,'IN',"NUMBER",0)) AS sum_in_number ,
SUM(DECODE(TYPE,'OUT',"NUMBER",0)) AS sum_out_number
FROM T
GROUP BY SNO;

[luckings]

1.select sno, sum(decode(type,'IN',number,0)) sum_in_number,
sum(decode(type,'OUT',number,0)) sum_out_number
from tablename group by sno;

2.select sno,sum(case when type='IN' then number else 0 end) sum_in_number,sum(case when type='OUT' then number else 0 end) sum_out_number from tablename group by sno;
这两种方法都可以满足你的要求，试试！

[陈字文]

[Quote=引用 9 楼 的回复:]
1.select sno, sum(decode(type,'IN',number,0)) sum_in_number,
sum(decode(type,'OUT',number,0)) sum_out_number
from tablename group by sno;

2.select sno,sum(case when type='IN' then number else 0 ……
[/Quote]

+1