針對(duì)CASE WHEN函數(shù)語(yǔ)句，實(shí)現(xiàn)簡(jiǎn)單CASE函數(shù)和CASE搜索函數(shù)兩種格式。
同時(shí)配合 SUM以及COUNT方法的使用

1、CASE 的兩種格式：?簡(jiǎn)單CASE函數(shù)和CASE搜索函數(shù)

**簡(jiǎn)單CASE函數(shù)**
CASE 條件參數(shù)名稱
    WHEN 參數(shù)值1 THEN '顯示值1'
    WHEN 參數(shù)值2 THEN '顯示值2'
    ...
ELSE '顯示其他值' END

**CASE搜索函數(shù)**
CASE 
    WHEN 條件參數(shù)名稱 = '參數(shù)值1' THEN '顯示值1'
    WHEN 條件參數(shù)名稱 = '參數(shù)值2' THEN '顯示值2'
    ...
ELSE '顯示其他值' END

兩種格式示例：
狀態(tài)：state
訂單號(hào)：orderId

**簡(jiǎn)單CASE函數(shù)**
SELECT
	orderId,
	CASE state
	    WHEN 1 THEN '啟動(dòng)'
	    WHEN 2 THEN '關(guān)閉'
	ELSE '未知狀態(tài)' END AS statusName
FROM t_table

**CASE搜索函數(shù)**
SELECT
	orderId,
	CASE 
	    WHEN state = '1' THEN '啟動(dòng)'
	    WHEN state = '2' THEN '關(guān)閉'
	ELSE '未知狀態(tài)' END AS stateName
FROM t_table

這兩種格式式，可以實(shí)現(xiàn)相同的功能。但是簡(jiǎn)單CASE函數(shù)和CASE搜索函數(shù)相比，功能方面會(huì)有些限制，比如寫(xiě)判斷式：

SELECT
	orderId,
	CASE 
	    WHEN state = '1' THEN '啟動(dòng)'
	    WHEN state = '2' THEN '關(guān)閉'
	    WHEN state IN ('3', '4') THEN '待解鎖'
	ELSE '未知狀態(tài)' END AS stateName
FROM t_table

若是多重時(shí)，WHEN 的 IN 條件中的值和下一個(gè) WHEN 的條件重合會(huì)被忽略，如下面的 “關(guān)閉” 狀態(tài)會(huì)被忽略，永遠(yuǎn)無(wú)法得到 “關(guān)閉”

SELECT
	orderId,
	CASE 
	    WHEN state = '1' THEN '啟動(dòng)'
	    WHEN state IN ('2', '3', '4') THEN '待解鎖'
	    WHEN state = '2' THEN '關(guān)閉'
	ELSE '未知狀態(tài)' END AS stateName
FROM t_table

2、同時(shí)配合 SUM以及COUNT方法的使用

（1）SUM函數(shù)

**簡(jiǎn)單CASE函數(shù)**
SUM(CASE 條件參數(shù)名稱
    	WHEN 參數(shù)值 THEN '顯示被求和值'
	ELSE 0 END
	) AS SUMAMT

**CASE搜索函數(shù)**
SUM(CASE 
    	WHEN 條件參數(shù)名稱 = '參數(shù)值' THEN '顯示被求和值'
	ELSE 0 END
	) AS SUMAMT

**或者**	
SUM(CASE 
    	WHEN 
    		條件參數(shù)名稱1 = '參數(shù)值1' AND 條件參數(shù)名稱2 = '參數(shù)值2' ...
    	THEN '顯示被求和值'
	ELSE 0 END
	) AS SUMAMT
	
**或者**	
SUM(CASE 
    	WHEN 
    		條件參數(shù)名稱1 IN ('參數(shù)值n') AND 條件參數(shù)名稱2 = '參數(shù)值2' ...
    	THEN '顯示被求和值'
	ELSE 0 END
	) AS SUMAMT

示例：?根據(jù)時(shí)間條件查詢值，有值時(shí)顯示 “被求和值”， 沒(méi)有值時(shí)，顯示 “null”
狀態(tài)：state
類型：t_type
金額：amt

**簡(jiǎn)單CASE函數(shù)**
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搜索函數(shù)**
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函數(shù)

**簡(jiǎn)單CASE函數(shù)**
COUNT(CASE 條件參數(shù)名稱
    	WHEN 參數(shù)值 THEN 1
	ELSE 0 END
	) AS COUNTAMT

**CASE搜索函數(shù)**
COUNT(CASE 
    	WHEN 條件參數(shù)名稱 = '參數(shù)值' THEN 1
	ELSE 0 END
	) AS COUNTAMT

**或者**	
COUNT(CASE 
    	WHEN 
    		條件參數(shù)名稱1 = '參數(shù)值1' AND 條件參數(shù)名稱2 = '參數(shù)值2' ...
    	THEN 1
	ELSE 0 END
	) AS COUNTAMT
	
**或者**	
COUNT(CASE 
    	WHEN 
    		條件參數(shù)名稱1 IN ('參數(shù)值n') AND 條件參數(shù)名稱2 = '參數(shù)值2' ...
    	THEN 1
	ELSE 0 END
	) AS COUNTAMT

示例：?根據(jù)時(shí)間條件查詢值，有值時(shí)顯示 “總條數(shù)值”， 沒(méi)有值時(shí)，顯示 “0”
狀態(tài)：state
類型：t_type
金額：amt文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-516209.html

**簡(jiǎn)單CASE函數(shù)**
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搜索函數(shù)**
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

到了這里，關(guān)于SQL之CASE WHEN函數(shù)語(yǔ)句多條件下使用詳解的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！