MySQL數(shù)據(jù)庫(kù)case?when?then?end的詳細(xì)使用方法
一、簡(jiǎn)介
今天我們主要是講講case…when…then…end的用法，它主要分成兩類：
- 簡(jiǎn)單Case函數(shù)
- Case搜索函數(shù)
假設(shè)我們數(shù)據(jù)庫(kù)有一個(gè)員工信息表表如下：
CREATE TABLE `tb_employee` ( `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵', `emp_code` int unsigned NOT NULL DEFAULT '0' COMMENT '員工編碼', `emp_name` varchar(20) NOT NULL DEFAULT '' COMMENT '員工姓名', `gender` char(1) NOT NULL DEFAULT '1' COMMENT '性別(1:男0:女)', `dep_code` int NOT NULL DEFAULT '0' COMMENT '部門(mén)', `job` varchar(20) NOT NULL DEFAULT '' COMMENT '工作', `age` tinyint NOT NULL DEFAULT '0' COMMENT '年齡', `salary` double(8,2) NOT NULL DEFAULT '0.00' COMMENT '工資', `hire_date` date DEFAULT NULL COMMENT '入職時(shí)間', `manage_code` int DEFAULT NULL COMMENT '所屬領(lǐng)導(dǎo)', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間', PRIMARY KEY (`id`), KEY `idx_emp_code` (`emp_code`), KEY `idx_manage_code` (`manage_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='員工信息表';
接下來(lái)希望你看文章時(shí)不要因?yàn)閟ql長(zhǎng)而害怕，都是些重復(fù)的東西而已，請(qǐng)放寬心態(tài)看待。
二、簡(jiǎn)單Case函數(shù)
2.1、語(yǔ)法定義
語(yǔ)法如下：
CASE '字段名'
WHEN '字段值1' THEN '結(jié)果1'
WHEN '字段值2' THEN '結(jié)果2'
WHEN '字段值3' THEN '結(jié)果3'
ELSE '其他結(jié)果'
END
字段名 就是數(shù)據(jù)庫(kù)表中字段， 字段值 就是這個(gè)字段存儲(chǔ)的值， 結(jié)果 就是你希望得到的結(jié)果。
2.2、簡(jiǎn)單函數(shù)形式
比如我們要 查詢一份基本的員工信息 ，數(shù)據(jù)庫(kù)里存儲(chǔ)的是1或者0，我們肯定不會(huì)顯示1或者0，而是對(duì)應(yīng)的性別（男或者女），這樣更加的直觀。從上面 tb_student 表的定義我們知道字段 gender ，1表示男，0表示女，默認(rèn)值是1，這個(gè)時(shí)候我們就可以利用 case…when…then…end 來(lái)實(shí)現(xiàn)
SELECT emp_code AS '員工編號(hào)', emp_name AS '員工姓名', (CASE gender WHEN 1 THEN '男' WHEN 0 THEN '女' ELSE '未知' END) AS '性別', salary AS '工資' FROM tb_employee;
還有些人覺(jué)得 else 可以不要，但是不建議這樣做，假設(shè)數(shù)據(jù)庫(kù)沒(méi)有設(shè)置默認(rèn)值，程序又沒(méi)有設(shè)置值，那就變成空了，又或者有個(gè)傻瓜蛋把 gender 的值改成了2呢？畢竟 else 是你的一個(gè)兜底。尤其是在一些字段可能會(huì)擴(kuò)展的類型的時(shí)候， else 就顯得很重要了。
一般會(huì)把 case 到 end 用括號(hào)包括，這樣也便于解讀或者使用別名等。
三、Case搜索函數(shù)
3.1、語(yǔ)法定義
語(yǔ)法如下：
CASE WHEN '表達(dá)式1' THEN '結(jié)果1'
WHEN '表達(dá)式2' THEN '結(jié)果2'
WHEN '表達(dá)式3' THEN '結(jié)果3'
ELSE '其他結(jié)果'
END
字段名 就是數(shù)據(jù)庫(kù)表中字段， 字段值 就是這個(gè)字段存儲(chǔ)的值， 結(jié)果 就是你希望得到的結(jié)果。在Case函數(shù)中，表達(dá)式可以使用 BETWEEN,LIKE,IS NULL,IN,EXISTS 等等
3.2、簡(jiǎn)單用法
比如我們還是用 查詢一份基本的員工信息 舉例看基本使用。
SELECT emp_code AS '員工編號(hào)', emp_name AS '員工姓名', (CASE WHEN gender=1 THEN '男' WHEN gender=0 THEN '女' ELSE '未知' END) AS '性別', salary AS '工資' FROM tb_employee;
這樣你會(huì)發(fā)現(xiàn)和上面簡(jiǎn)單Case函數(shù)形式差別很小，確實(shí)，如果只是等值表達(dá)式，區(qū)別很小，并且簡(jiǎn)單表達(dá)式還簡(jiǎn)單些。這里這么寫(xiě)只是先混個(gè)臉熟，根本沒(méi)有把表達(dá)式的作用發(fā)揮出來(lái)。
3.3、分組
老板想看看公司里員工的薪資架構(gòu)是否合理， 需要提供一份明細(xì)，查詢每個(gè)人對(duì)應(yīng)的級(jí)別 ，級(jí)別規(guī)劃如下：
| 工資范圍 | 工資級(jí)別 |
|---|---|
| 員工工資小于3000的 | 入門(mén) |
| 員工工資大于等于3000并且小于15000的 | 初級(jí) |
| 員工工資大于等于15000并且小于25000的 | 中級(jí) |
| 員工工資大于等于25000并且小于50000的 | 高級(jí) |
| 員工工資大于等于50000 | 特級(jí) |
則我們可以使用 case…when…then…end 這一語(yǔ)法完成這個(gè)查詢。
SELECT
emp_code AS '員工編號(hào)',
emp_name AS '員工姓名',
salary AS '員工工資',
(CASE
WHEN salary < 3000 THEN '入門(mén)級(jí)'
WHEN salary >= 3000 AND salary < 15000 THEN '初級(jí)'
WHEN salary >= 15000 AND salary < 25000 THEN '中級(jí)'
WHEN salary >= 25000 AND salary < 50000 THEN '高級(jí)'
ELSE '特級(jí)'
END) AS '工資級(jí)別'
FROM tb_employee;
這里的表達(dá)式，使用了算術(shù)表達(dá)式，and表達(dá)式，還要between…and 表達(dá)式，這里只是告訴大家可以用，實(shí)際沒(méi)必要混著用。
3.4、分組+計(jì)數(shù)
老板想看看 公司對(duì)應(yīng)的每個(gè)工資級(jí)別分別有多少人 。
SELECT
(CASE
WHEN salary < 3000 THEN '入門(mén)級(jí)'
WHEN salary >= 3000 AND salary < 15000 THEN '初級(jí)'
WHEN salary >= 15000 AND salary < 25000 THEN '中級(jí)'
WHEN salary >= 25000 AND salary < 50000 THEN '高級(jí)'
ELSE '特級(jí)'
END) as 'levels',
count(*) AS '總?cè)藬?shù)'
FROM tb_employee
GROUP BY levels;
如果老板還想 細(xì)分到每個(gè)部門(mén)，及每個(gè)部門(mén)對(duì)應(yīng)工資級(jí)別的總?cè)藬?shù) ，假設(shè)部門(mén)編號(hào)從10到14分別對(duì)應(yīng)則：
| 編號(hào) | 部門(mén) |
|---|---|
| 10 | 總經(jīng)辦 |
| 11 | 財(cái)務(wù) |
| 12 | 技術(shù) |
| 13 | 測(cè)試 |
| 14 | 運(yùn)維 |
那么我們只需要先按部門(mén)分組，再按工資級(jí)別分組即可
SELECT
dep_code AS '部門(mén)編號(hào)',
(CASE
WHEN dep_code=10 THEN '總經(jīng)辦'
WHEN dep_code=11 THEN '財(cái)務(wù)'
WHEN dep_code=12 THEN '技術(shù)'
WHEN dep_code=13 THEN '測(cè)試'
WHEN dep_code=14 THEN '運(yùn)維'
ELSE '其他'
END) AS '部門(mén)',
(CASE
WHEN salary < 3000 THEN '入門(mén)級(jí)'
WHEN salary >= 3000 AND salary < 15000 THEN '初級(jí)'
WHEN salary >= 15000 AND salary < 25000 THEN '中級(jí)'
WHEN salary >= 25000 AND salary < 50000 THEN '高級(jí)'
ELSE '特級(jí)'
END) AS 'levels',
count(*) as '總?cè)藬?shù)'
FROM tb_employee
GROUP BY dep_code,levels;
實(shí)際中對(duì)應(yīng)部門(mén)名稱肯定是以連表查詢居多，我這里是為了演示，順便加深 case…when…then…end 用法的使用
3.5、分組+匯總
如果老板現(xiàn)在想知道， 每個(gè)部門(mén)的總工資，及每個(gè)部門(mén)中每個(gè)工資級(jí)別每個(gè)月總工資是多少 。小伙伴們想到的可能是先按部門(mén)分組，再按性別分組，然后再匯總。如果是一條記錄顯示這個(gè)結(jié)果，我相信很多小伙伴也不知道怎么去查詢。
我們不著急，我們先查個(gè)簡(jiǎn)單的，查詢每個(gè)部門(mén)的男生總數(shù)和女生總數(shù)，以及部門(mén)的總?cè)藬?shù)。那么 case…when…then…end 的作用又來(lái)了。
SELECT
dep_code AS '部門(mén)編號(hào)',
(CASE
WHEN dep_code=10 THEN '總經(jīng)辦'
WHEN dep_code=11 THEN '財(cái)務(wù)'
WHEN dep_code=12 THEN '技術(shù)'
WHEN dep_code=13 THEN '測(cè)試'
WHEN dep_code=14 THEN '運(yùn)維'
ELSE '其他'
END) AS '部門(mén)',
SUM((CASE WHEN gender = 1 THEN 1 ELSE 0 END)) AS '男生人數(shù)',
SUM((CASE WHEN gender = 0 THEN 1 ELSE 0 END)) AS '女生人數(shù)',
COUNT(*) AS '部門(mén)總?cè)藬?shù)'
FROM
tb_employee
GROUP BY dep_code;
也許即算看了代碼，也許還是有不理解的，為什么兩個(gè)總數(shù)在一行。
- count(*)按部門(mén)分組，同一個(gè)部門(mén)的每一條記錄都會(huì)加入結(jié)果集
- case…when…then…end這個(gè)是同一個(gè)部門(mén)中，只有滿足條件才會(huì)記錄到結(jié)果集，我們這里滿足就記為1，不滿足，記為0，然后使用sum函數(shù)匯總
了解了上面這個(gè)后，我們之前那個(gè)需求 每個(gè)部門(mén)的總工資，及每個(gè)部門(mén)中每個(gè)工資級(jí)別每個(gè)月總工資是多少 就容易理解了，查詢?nèi)缦拢?/p>
SELECT
dep_code AS '部門(mén)編號(hào)',
(CASE
WHEN dep_code=10 THEN '總經(jīng)辦'
WHEN dep_code=11 THEN '財(cái)務(wù)'
WHEN dep_code=12 THEN '技術(shù)'
WHEN dep_code=13 THEN '測(cè)試'
WHEN dep_code=14 THEN '運(yùn)維'
ELSE '其他'
END) AS '部門(mén)',
SUM(salary) AS '總工資',
SUM((CASE WHEN salary <= 3000 THEN salary ELSE 0 END)) AS '入門(mén)總工資',
SUM((CASE WHEN salary > 3000 AND salary < 15000 THEN salary ELSE 0 END)) AS '初級(jí)總工資',
SUM((CASE WHEN salary >= 15000 AND salary < 25000 THEN salary ELSE 0 END)) AS '中級(jí)總工資',
SUM((CASE WHEN salary >= 25000 AND salary <= 50000 THEN salary ELSE 0 END)) AS '高級(jí)總工資',
SUM((CASE WHEN salary > 50000 THEN salary ELSE 0 END)) AS '特級(jí)總工資'
FROM
tb_employee
GROUP BY dep_code;
其實(shí)還算可以更詳細(xì) 每個(gè)部門(mén)的總?cè)藬?shù)，總工資，及每個(gè)部門(mén)中每個(gè)工資級(jí)別的人數(shù)及每個(gè)級(jí)別對(duì)應(yīng)的總工資是多少
SELECT
dep_code AS '部門(mén)編號(hào)',
(CASE
WHEN dep_code=10 THEN '總經(jīng)辦'
WHEN dep_code=11 THEN '財(cái)務(wù)'
WHEN dep_code=12 THEN '技術(shù)'
WHEN dep_code=13 THEN '測(cè)試'
WHEN dep_code=14 THEN '運(yùn)維'
ELSE '其他'
END) AS '部門(mén)',
COUNT(*) AS '總?cè)藬?shù)',
SUM(salary) AS '總工資',
SUM((CASE WHEN salary <= 3000 THEN 1 ELSE 0 END)) AS '入門(mén)總?cè)藬?shù)',
SUM((CASE WHEN salary > 3000 AND salary < 15000 THEN 1 ELSE 0 END)) AS '初級(jí)總?cè)藬?shù)',
SUM((CASE WHEN salary >= 15000 AND salary < 25000 THEN 1 ELSE 0 END)) AS '中級(jí)總?cè)藬?shù)',
SUM((CASE WHEN salary >= 25000 AND salary <= 50000 THEN 1 ELSE 0 END)) AS '高級(jí)總?cè)藬?shù)',
SUM((CASE WHEN salary > 50000 THEN 1 ELSE 0 END)) AS '特級(jí)總?cè)藬?shù)',
SUM((CASE WHEN salary <= 3000 THEN salary ELSE 0 END)) AS '入門(mén)總工資',
SUM((CASE WHEN salary > 3000 AND salary < 15000 THEN salary ELSE 0 END)) AS '初級(jí)總工資',
SUM((CASE WHEN salary >= 15000 AND salary < 25000 THEN salary ELSE 0 END)) AS '中級(jí)總工資',
SUM((CASE WHEN salary >= 25000 AND salary <= 50000 THEN salary ELSE 0 END)) AS '高級(jí)總工資',
SUM((CASE WHEN salary > 50000 THEN salary ELSE 0 END)) AS '特級(jí)總工資'
FROM
tb_employee
GROUP BY dep_code;
相當(dāng)于兩個(gè)例子合并了，還可以計(jì)算平均工資等就不一一列舉了。
3.6、更新語(yǔ)句
公司部門(mén)編號(hào)從10到20，公司對(duì)員工的工資進(jìn)行調(diào)整，除去部門(mén)10以外
| 工資范圍 | 工資級(jí)別 |
|---|---|
| 員工工資小于3000的 | 漲薪400 |
| 員工工資大于等于3000并且小于15000的 | 漲薪20% |
| 員工工資大于等于15000并且小于25000的 | 漲薪10% |
| 員工工資大于等于25000并且小于50000的 | 不變 |
| 員工工資大于等于50000 | 降薪10% |
UPDATE tb_employee
SET
salary = (CASE
WHEN salary <= 3000 THEN salary + 400
WHEN salary > 3000 AND salary <= 15000 THEN salary * 1.2
WHEN salary > 15000 AND salary < 25000 THEN salary * 1.1
WHEN salary > 50000 THEN salary * 0.9
ELSE salary
END)
where dep_code > 10;
3.7、子查詢
比如對(duì)賬時(shí)有本地記錄 tb_local_record 和外部記錄 tb_outside_record ，通過(guò)查詢看哪些本地記錄沒(méi)有對(duì)應(yīng)的外部記錄。
SELECT tranSeq as '交易流水', (CASE WHEN tranSeq IN (SELECT tranSeq FROM tb_outside_record) THEN '匹配' ELSE '未匹配' END) as '是否匹配' FROM tb_local_record;
或者
SELECT lr.tranSeq as '交易流水', (CASE WHEN EXISTS (SELECT osr.tranSeq FROM tb_outside_record osr WHERE osr.tranSeq = lr.tranSeq) THEN '匹配' ELSE '未匹配' END) as '是否匹配' FROM tb_local_record lr;
結(jié)語(yǔ)
case…when…then…end的用法還有很多，比如還能聯(lián)合count函數(shù)，但是一般有以上的方式，基本上就夠你工作所需了。
到此這篇關(guān)于MySQL數(shù)據(jù)庫(kù)case when then end詳細(xì)使用方法的文章就介紹到這了,更多相關(guān)MySQL case when then end內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL超詳細(xì)安裝配置超詳細(xì)圖文教程(親測(cè)有效)
這篇文章詳細(xì)介紹了如何下載、配置和安裝MySQL,包括設(shè)置環(huán)境變量、初始化my.ini文件、開(kāi)啟MySQL服務(wù)以及設(shè)置密碼,此外,還介紹了如何使用Navicat工具連接MySQL數(shù)據(jù)庫(kù),感興趣的朋友跟隨小編一起看看吧2024-11-11
關(guān)于for update和lock in share mode的區(qū)別及說(shuō)明
這篇文章主要介紹了關(guān)于for update和lock in share mode的區(qū)別及說(shuō)明,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-08-08
mysql odbc字符集設(shè)置(中文顯示亂碼),碰到這個(gè)問(wèn)題的朋友可以參考下。2011-08-08
CentOS7安裝MySQL8的超級(jí)詳細(xì)教程(無(wú)坑!)
我們?cè)贚inux系統(tǒng)中,如果要使用關(guān)系型數(shù)據(jù)庫(kù)的話,基本都是用的mysql,這篇文章主要給大家介紹了關(guān)于CentOS7安裝MySQL8的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-06-06
這篇文章主要介紹了MySQL load語(yǔ)句詳細(xì)介紹,本文講解了load的基本語(yǔ)法、文件的路徑、配置選項(xiàng)、STARTING LINES選項(xiàng)、TERMINATED LINES選項(xiàng)等內(nèi)容,需要的朋友可以參考下2014-12-12
MySQL授權(quán)命令grant的使用方法小結(jié)
這篇文章主要介紹了MySQL授權(quán)命令grant的使用方法,本文實(shí)例，運(yùn)行于?MySQL?5.0?及以上版本，介紹了MySQL?賦予用戶權(quán)限命令的簡(jiǎn)單格式，本文給大家介紹的非常詳細(xì)，需要的朋友參考下吧2021-12-12
這篇文章主要介紹了MySQL kill不掉線程的原因，幫助大家更好的理解和學(xué)習(xí)使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下2021-05-05
這篇文章主要介紹了MySQL基本運(yùn)維命令,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-01-01
mysql 5.1版本修改密碼及遠(yuǎn)程登錄mysql數(shù)據(jù)庫(kù)的方法
這篇文章主要介紹了mysql 5.1版本修改密碼及遠(yuǎn)程登錄mysql數(shù)據(jù)庫(kù)的方法,需要的朋友可以參考下2017-04-04
詳解JDBC數(shù)據(jù)庫(kù)鏈接及相關(guān)方法的封裝
這篇文章主要介紹了詳解JDBC數(shù)據(jù)庫(kù)鏈接及相關(guān)方法的封裝的相關(guān)資料,下面是封裝的具體類，用到了泛型和反射，希望能幫助到大家，需要的朋友可以參考下2017-08-08