17,086
社区成员
发帖
与我相关
我的任务
分享
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))
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