「Oracle」SELECT CASE WHEN式、 GROUP BY CASE WHEN 式の書き方
1. SELECT CASE WHEN 式
SQL構文
SELECT grade, COUNT (CASE WHEN depid = 1 THEN 1
ELSE NULL
END) 開発部社員数,
COUNT (CASE WHEN depid = 2 THEN 1
ELSE NULL
END) デザイン部社員数
FROM employees GROUP BY grade;
2. WHERE CASE WHEN 式
SQL構文
SELECT T2.*, T1.*
FROM T1, T2
WHERE (CASE WHEN T2.COMPARE_TYPE = 'A’ AND
T1.SOME_TYPE LIKE 'NOTHING%’
THEN 1
WHEN T2.COMPARE_TYPE != 'A’ AND
T1.SOME_TYPE NOT LIKE 'NOTHING%’
THEN 1
ELSE 0
END) = 1
3.GROUP BY CASE WHEN 式
SQL構文
SELECT
CASE WHEN cost <= 200 THEN '1’ WHEN cost > 500 AND cost <= 700 THEN '2’ WHEN cost > 700 AND cost <= 900 THEN '3’ WHEN cost > 900 AND cost <= 1000 THEN '4’
ELSE NULL END cost_class, — エイリアス
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN cost <= 500 THEN '1’ WHEN cost > 500 AND cost <= 700 THEN '2’ WHEN cost > 700 AND cost <= 900 THEN '3’ WHEN cost > 900 AND cost <= 1000 THEN '4’
ELSE NULL END;