SQL之CASE WHEN函数语句多条件下使用详解
作者:mmseoamin日期:2023-12-13

针对CASE WHEN函数语句,实现简单CASE函数和CASE搜索函数两种格式。

同时配合 SUM以及COUNT方法的使用

1、CASE 的两种格式: 简单CASE函数和CASE搜索函数

**简单CASE函数**
CASE 条件参数名称
    WHEN 参数值1 THEN '显示值1'
    WHEN 参数值2 THEN '显示值2'
    ...
ELSE '显示其他值' END
**CASE搜索函数**
CASE 
    WHEN 条件参数名称 = '参数值1' THEN '显示值1'
    WHEN 条件参数名称 = '参数值2' THEN '显示值2'
    ...
ELSE '显示其他值' END

两种格式示例:

状态:state

订单号:orderId

**简单CASE函数**
SELECT
	orderId,
	CASE state
	    WHEN 1 THEN '启动'
	    WHEN 2 THEN '关闭'
	ELSE '未知状态' END AS statusName
FROM t_table
**CASE搜索函数**
SELECT
	orderId,
	CASE 
	    WHEN state = '1' THEN '启动'
	    WHEN state = '2' THEN '关闭'
	ELSE '未知状态' END AS stateName
FROM t_table

这两种格式式,可以实现相同的功能。但是简单CASE函数和CASE搜索函数相比,功能方面会有些限制,比如写判断式:

SELECT
	orderId,
	CASE 
	    WHEN state = '1' THEN '启动'
	    WHEN state = '2' THEN '关闭'
	    WHEN state IN ('3', '4') THEN '待解锁'
	ELSE '未知状态' END AS stateName
FROM t_table

若是多重时,WHEN 的 IN 条件中的值和下一个 WHEN 的条件重合会被忽略,如下面的 “关闭” 状态会被忽略,永远无法得到 “关闭”

SELECT
	orderId,
	CASE 
	    WHEN state = '1' THEN '启动'
	    WHEN state IN ('2', '3', '4') THEN '待解锁'
	    WHEN state = '2' THEN '关闭'
	ELSE '未知状态' END AS stateName
FROM t_table

2、同时配合 SUM以及COUNT方法的使用

(1)SUM函数

**简单CASE函数**
SUM(CASE 条件参数名称
    	WHEN 参数值 THEN '显示被求和值'
	ELSE 0 END
	) AS SUMAMT
**CASE搜索函数**
SUM(CASE 
    	WHEN 条件参数名称 = '参数值' THEN '显示被求和值'
	ELSE 0 END
	) AS SUMAMT
**或者**	
SUM(CASE 
    	WHEN 
    		条件参数名称1 = '参数值1' AND 条件参数名称2 = '参数值2' ...
    	THEN '显示被求和值'
	ELSE 0 END
	) AS SUMAMT
	
**或者**	
SUM(CASE 
    	WHEN 
    		条件参数名称1 IN ('参数值n') AND 条件参数名称2 = '参数值2' ...
    	THEN '显示被求和值'
	ELSE 0 END
	) AS SUMAMT

示例: 根据时间条件查询值,有值时显示 “被求和值”, 没有值时,显示 “null”

状态:state

类型:t_type

金额:amt

**简单CASE函数**
SELECT
	SUM(CASE state
	    WHEN 1 THEN amt
	ELSE 0 END 
	) AS sumAmt
FROM 
	t_table
WHERE
	core_time >= '2020-07-01 00:00:00'
AND core_time <= '2020-07-20 23:59:59'
GROUP BY core_time
**CASE搜索函数**
SELECT
	SUM(CASE 
	    WHEN state = '1' THEN amt
	ELSE 0 END 
	) AS sumAmt
FROM 
	t_table
WHERE
	core_time >= '2020-07-01 00:00:00'
AND core_time <= '2020-07-20 23:59:59'
GROUP BY core_time
**或者**
SELECT
	SUM(CASE 
	    WHEN state = '1' AND t_type = '2' THEN amt
	ELSE 0 END 
	) AS sumAmt
FROM 
	t_table
WHERE
	core_time >= '2020-07-01 00:00:00'
AND core_time <= '2020-07-20 23:59:59'
GROUP BY core_time
**或者**
SELECT
	SUM(CASE 
	    WHEN state IN ('2', '3', '4') AND t_type = '2' THEN amt
	ELSE 0 END 
	) AS sumAmt
FROM 
	t_table
WHERE
	core_time >= '2020-07-01 00:00:00'
AND core_time <= '2020-07-20 23:59:59'
GROUP BY core_time

(2)COUNT函数

**简单CASE函数**
COUNT(CASE 条件参数名称
    	WHEN 参数值 THEN 1
	ELSE 0 END
	) AS COUNTAMT
**CASE搜索函数**
COUNT(CASE 
    	WHEN 条件参数名称 = '参数值' THEN 1
	ELSE 0 END
	) AS COUNTAMT
**或者**	
COUNT(CASE 
    	WHEN 
    		条件参数名称1 = '参数值1' AND 条件参数名称2 = '参数值2' ...
    	THEN 1
	ELSE 0 END
	) AS COUNTAMT
	
**或者**	
COUNT(CASE 
    	WHEN 
    		条件参数名称1 IN ('参数值n') AND 条件参数名称2 = '参数值2' ...
    	THEN 1
	ELSE 0 END
	) AS COUNTAMT

示例: 根据时间条件查询值,有值时显示 “总条数值”, 没有值时,显示 “0”

状态:state

类型:t_type

金额:amt

**简单CASE函数**
SELECT
	COUNT(CASE state
	    WHEN 1 THEN 1
	ELSE 0 END 
	) AS countAmt
FROM 
	t_table
WHERE
	core_time >= '2020-07-01 00:00:00'
AND core_time <= '2020-07-20 23:59:59'
GROUP BY core_time
**CASE搜索函数**
SELECT
	COUNT(CASE 
	    WHEN state = '1' THEN 1
	ELSE 0 END 
	) AS countAmt
FROM 
	t_table
WHERE
	core_time >= '2020-07-01 00:00:00'
AND core_time <= '2020-07-20 23:59:59'
GROUP BY core_time
**或者**
SELECT
	COUNT(CASE 
	    WHEN state = '1' AND t_type = '2' THEN 1
	ELSE 0 END 
	) AS countAmt
FROM 
	t_table
WHERE
	core_time >= '2020-07-01 00:00:00'
AND core_time <= '2020-07-20 23:59:59'
GROUP BY core_time
**或者**
SELECT
	COUNT(CASE 
	    WHEN state IN ('2', '3', '4') AND t_type = '2' THEN 1
	ELSE 0 END 
	) AS countAmt
FROM 
	t_table
WHERE
	core_time >= '2020-07-01 00:00:00'
AND core_time <= '2020-07-20 23:59:59'
GROUP BY core_time