Oracle中Case When的用法

一、Case的两种表达式

1.1、简单的case表达式

SELECT 
 CASE 列名 
  WHEN 值1 THEN 結果1 
  WHEN 值2 THEN 結果2 
  ELSE 结果3
 END 
FROM table1;

1.2、检索的cse表达式

SELECT 
 CASE 
  WHEN 条件1 THEN 条件1的結果
  WHEN 条件2 THEN 条件2的結果
  ELSE 条件1,2以外的结果
 END 
FROM table1;

二、Case的不同用法

2.1、作为求个数【COUNT】、求和【SUM】的条件判断

SELECT
	COUNT( CASE WHEN AGE = 18 THEN '18岁的学生个数' END) EIGHTENN,
	COUNT( CASE WHEN AGE = 19 THEN '19岁的学生个数' END) NINETEEN
FROM
	"PeopleInfo"

比如获取指定时间段内，每个时间段的设备报警条数

SELECT
	EQUIPMENT,
	COUNT( CASE WHEN CHECKDATETIME = '2021-11-02 08' THEN EQUIPMENT END ) AS ONE,
	COUNT( CASE WHEN CHECKDATETIME = '2021-11-02 09' THEN EQUIPMENT END ) AS TWO,
	COUNT( CASE WHEN CHECKDATETIME = '2021-11-02 10' THEN EQUIPMENT END ) AS THREE,
	COUNT( CASE WHEN CHECKDATETIME = '2021-11-02 11' THEN EQUIPMENT END ) AS FOUR,
	COUNT( CASE WHEN CHECKDATETIME = '2021-11-02 12' THEN EQUIPMENT END ) AS FIVE,
	COUNT( CASE WHEN CHECKDATETIME = '2021-11-02 13' THEN EQUIPMENT END ) AS SIX,
	COUNT( CASE WHEN CHECKDATETIME = '2021-11-02 14' THEN EQUIPMENT END ) AS SEVEN,
	COUNT( CASE WHEN CHECKDATETIME = '2021-11-02 15' THEN EQUIPMENT END ) AS EIGHT,
	COUNT( CASE WHEN CHECKDATETIME = '2021-11-02 16' THEN EQUIPMENT END ) AS NINE,
	COUNT( CASE WHEN CHECKDATETIME = '2021-11-02 17' THEN EQUIPMENT END ) AS TEN,
	COUNT( CASE WHEN CHECKDATETIME = '2021-11-02 18' THEN EQUIPMENT END ) AS ELEVEN,
	COUNT( CASE WHEN CHECKDATETIME = '2021-11-02 19' THEN EQUIPMENT END ) AS TWELVE 
FROM
	(
	SELECT
		EQUIPMENT,
		TO_CHAR( CHECKDATETIME, 'yyyy-MM-dd hh24' ) CHECKDATETIME 
	FROM
		TESTEQUIPMENT
	WHERE
		AND CHECKDATETIME BETWEEN TO_DATE( '2021-11-02 08:00:00', 'yyyy-MM-dd hh24:mi:ss' ) 
		AND TO_DATE( '2021-11-02 19:59:59', 'yyyy-MM-dd hh24:mi:ss' ) 
	) 
GROUP BY
	EQUIPMENT 
ORDER BY
	EQUIPMENT ASC

2.2、作为WHERE后的多条件判断

SELECT
	* 
FROM
	"PeopleInfo" 
WHERE
	( CASE WHEN AGE = 18 AND SEX = 0 THEN 1 WHEN AGE = 19 AND SEX = 1 THEN 2 ELSE 0 END) = 1

2.3、作为分组Group by的条件判断

SELECT
	CASE WHEN AGE=18 THEN '18'   
	     WHEN AGE=19 THEN '19'
	END AGE,
	COUNT(*)

FROM
	"PeopleInfo"
	
GROUP BY 
CASE WHEN AGE=18  THEN '18'
     WHEN AGE=19 THEN '19' 
END 

  

三、示例

3.0、表内容

3.1、简单Case表达式

SELECT NAME,
CASE
		SEX 
		WHEN 0 THEN
		'女' 
		WHEN 1 THEN
		'男' 
		END Sex
FROM
	"PeopleInfo"

3.2、检索Case表达式

SELECT ID,
CASE
		WHEN SEX=0 AND AGE<=18 THEN NAME||'(女)'||AGE||'岁'
		ELSE NAME||AGE||'岁'||'(不满足小于等于18岁的女生条件)'
		END DETAIL
FROM
	"PeopleInfo"

四、其他资料

CASE Expressions (oracle.com)https://docs.oracle.com/cd/E11882_01/server.112/e41084/expressions004.htm#SQLRF20037Oracle CASEを使って条件をわける【SQL】 | Oracle初心者でもスッキリわかる (sql-oracle.com)https://sql-oracle.com/?p=466Oracle CASE WHEN 用法介绍https://www.cnblogs.com/eshizhan/archive/2012/04/06/2435493.html