我有一个表,表结构如下
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语句怎么写?谢谢
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语句怎么写?谢谢
解决方案 »
- ==上海著名外企: 招聘ERP工程师==
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- 关于多行转字符串
- 求一个sql,大虾帮个忙。
- 关于&的使用问题.关于数据字典问题
- 为什么SGA的最大大小要比总量大呢。
- 刚到Oracle版本.准备天天送分:顺便学习,第四个问题:SQL 中的"sp_executesql" Oracle 是怎么写的呢??
- 怎样完全卸载Oracle8
- 求教ORACLE8的安装方法!急!
- 如何在sql语句中将时间准确到小时??
- telnet oracle时,报错,在线等
- 一条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
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
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
(select sum(snum) from a where stype = 'out') sun_out_number from a ;
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;
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;
这两种方法都可以满足你的要求,试试!