我有一个表,表结构如下
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语句怎么写?谢谢
解决方案 »
- 关于LIKE的问题
- 无法解释的删除触发器的一个奇怪现象
- SOS: 有关mod在Oracle中很奇怪的问题.在线等待
- 怎样验证触发器?
- 下面是一句老版的sql语句 ,问问大虾们可不可以优化而提高执行速度?
- Sql 语句求助:有类似 Max() 的“逻辑或”的聚合函数吗?
- 我创建了一个用户,设置了他的默认表空间,为什么按这个用户名导入数据的时候,数据并没有保存到他默认的表空间里去?
- JDK1.3 和JDK1.4这两个环境下,连接Oracle数据库有什么区别?报错:java.lang.NoClassDefFoundError: java/sql/Savepoint
- 这个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;
这两种方法都可以满足你的要求,试试!