例子:
在一个表(TABLENAME)中由于一个字段(FLAG)的取值不同, 对另一个字段(VALUE)分别作多次sum.
SELECT ID, NVL(SUM(
CASE
WHEN FLAG IS NULL
THEN VALUE
END ), 0) VALUE_A, nvl(SUM(
CASE
WHEN FLAG IS NOT NULL
THEN VALUE
END ), 0) VALUE_B
FROM TABLENAME
GROUP BY ID
结果如下:
ID VALUE_A VALUE_B
1 100010 0
4 100000 100000
5 0 100000
另外:
1. case
SELECT LogID,UserID,LogTime,Url,Description,(
case
when OperateType = 0
then '新增'
when OperateType=1
then '修改'
else '删除'
end ) FROM LOG
2.DECODE的写法:
SELECT LogID,UserID,LogTime,Url,Description,
decode(OperateType,0,'新增',1,'修改','删除') OperationName
FROM LOG
语句嵌套:
SELECT A.trhcd ,B.kon_sei_gaku
FROM KKYM010 A JOIN BSHT100 B ON A.trhcd = B.trhcd1
AND B.SEI_SM_YMD =
(SELECT MAX(SEI_SM_YMD)
FROM BSHT100
WHERE BSHT100.SEI_SM_YMD < (
CASE WHEN A.sei_sm_dd IS NULL THEN TO_CHAR(TO_NUMBER(BSHT100.SEI_SM_YMD)+1)
WHEN A.sei_sm_dd IS NOT NULL THEN (DECODE(A.sei_sm_dd, 99, '20061201', '20051220'))
END)
AND BSHT100.TRHCD1 = A.TRHCD )
WHERE A.tkskbn IS NOT NULL AND A.uri_kj_flg = 1 ORDER BY A.trhcd