MySQL中關(guān)于case when的用法

更新時間：2024年06月19日 09:45:23 作者：嘆人間，美中不足今方信

這篇文章主要介紹了MySQL中關(guān)于case when的用法,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

MySQL的case when語法有兩種

1.簡單函數(shù)

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

2.搜索函數(shù)

CASE WHEN [expr] THEN [result1]…ELSE [default] END

這兩種語法有什么區(qū)別呢？

簡單函數(shù)

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END???????

枚舉這個字段所有可能的值*

SELECT
	NAME '英雄',
	CASE NAME
		WHEN '德萊文' THEN
			'斧子'
		WHEN '德瑪西亞-蓋倫' THEN
			'大寶劍'
		WHEN '暗夜獵手-VN' THEN
			'弩'
		ELSE
			'無'
	END '裝備'
FROM
	user_info;

這里寫圖片描述

搜索函數(shù)

CASE WHEN [expr] THEN [result1]…ELSE [default] END???????

搜索函數(shù)可以寫判斷，并且搜索函數(shù)只會返回第一個符合條件的值，其他case被忽略

# when 表達式中可以使用 and 連接條件
SELECT
	NAME '英雄',
	age '年齡',
	CASE
		WHEN age < 18 THEN
			'少年'
		WHEN age < 30 THEN
			'青年'
		WHEN age >= 30
		AND age < 50 THEN
			'中年'
		ELSE
			'老年'
	END '狀態(tài)'
FROM
	user_info;

這里寫圖片描述

聚合函數(shù)sum配合case when的簡單函數(shù)實現(xiàn)多表left join的行轉(zhuǎn)列

注：

曾經(jīng)有個愛學(xué)習(xí)的路人問我，“那個sum()只是為了好看一點嗎？”，left join會以左表為主，連接右表時，得到所有匹配的數(shù)據(jù)，再group by時只會保留一行數(shù)據(jù)，因此case when時要借助sum函數(shù)，保留其他列的和。

如果你還是不明白的話，那就親手實踐一下，只保留left join看一下結(jié)果，再group by，看一下結(jié)果。

例如下面的案例：

學(xué)生表/課程表/成績表，三個表left join查詢每個學(xué)生所有科目的成績，使每個學(xué)生及其各科成績一行展示。

SELECT
	st.stu_id '學(xué)號',
	st.stu_name '姓名',
	sum(
		CASE co.course_name
		WHEN '大學(xué)語文' THEN
			sc.scores
		ELSE
			0
		END
	) '大學(xué)語文',
	sum(
		CASE co.course_name
		WHEN '新視野英語' THEN
			sc.scores
		ELSE
			0
		END
	) '新視野英語',
	sum(
		CASE co.course_name
		WHEN '離散數(shù)學(xué)' THEN
			sc.scores
		ELSE
			0
		END
	) '離散數(shù)學(xué)',
	sum(
		CASE co.course_name
		WHEN '概率論與數(shù)理統(tǒng)計' THEN
			sc.scores
		ELSE
			0
		END
	) '概率論與數(shù)理統(tǒng)計',
	sum(
		CASE co.course_name
		WHEN '線性代數(shù)' THEN
			sc.scores
		ELSE
			0
		END
	) '線性代數(shù)',
	sum(
		CASE co.course_name
		WHEN '高等數(shù)學(xué)' THEN
			sc.scores
		ELSE
			0
		END
	) '高等數(shù)學(xué)'
FROM
	edu_student st
LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
LEFT JOIN edu_courses co ON co.course_no = sc.course_no
GROUP BY
	st.stu_id
ORDER BY
	NULL;

這里寫圖片描述

行轉(zhuǎn)列測試數(shù)據(jù)

-- 創(chuàng)建表  學(xué)生表
CREATE TABLE `edu_student` (
	`stu_id` VARCHAR (16) NOT NULL COMMENT '學(xué)號',
	`stu_name` VARCHAR (20) NOT NULL COMMENT '學(xué)生姓名',
	PRIMARY KEY (`stu_id`)
) COMMENT = '學(xué)生表' ENGINE = INNODB;

-- 課程表 
CREATE TABLE `edu_courses` (
	`course_no` VARCHAR (20) NOT NULL COMMENT '課程編號',
	`course_name` VARCHAR (100) NOT NULL COMMENT '課程名稱',
	PRIMARY KEY (`course_no`)
) COMMENT = '課程表' ENGINE = INNODB;

-- 成績表
CREATE TABLE `edu_score` (
	`stu_id` VARCHAR (16) NOT NULL COMMENT '學(xué)號',
	`course_no` VARCHAR (20) NOT NULL COMMENT '課程編號',
	`scores` FLOAT NULL DEFAULT NULL COMMENT '得分',
	PRIMARY KEY (`stu_id`, `course_no`)
) COMMENT = '成績表' ENGINE = INNODB;

-- 插入數(shù)據(jù)

-- 學(xué)生表數(shù)據(jù)

INSERT INTO edu_student (stu_id, stu_name)
VALUES
	('1001', '盲僧'),
	('1002', '趙信'),
	('1003', '皇子'),
	('1004', '寒冰'),
	('1005', '蠻王'),
	('1006', '狐貍');

-- 課程表數(shù)據(jù) 
INSERT INTO edu_courses (course_no, course_name)
VALUES
	('C001', '大學(xué)語文'),
	('C002', '新視野英語'),
	('C003', '離散數(shù)學(xué)'),
	(
		'C004',
		'概率論與數(shù)理統(tǒng)計'
	),
	('C005', '線性代數(shù)'),
	('C006', '高等數(shù)學(xué)');

-- 成績表數(shù)據(jù)
INSERT INTO edu_score (stu_id, course_no, scores)
VALUES
	('1001', 'C001', 67),	('1002', 'C001', 68),	('1003', 'C001', 69),	('1004', 'C001', 70),	('1005', 'C001', 71),
	('1006', 'C001', 72),	('1001', 'C002', 87),	('1002', 'C002', 88),	('1003', 'C002', 89),	('1004', 'C002', 90),
	('1005', 'C002', 91),	('1006', 'C002', 92),	('1001', 'C003', 83),	('1002', 'C003', 84),	('1003', 'C003', 85),
	('1004', 'C003', 86),	('1005', 'C003', 87),	('1006', 'C003', 88),	('1001', 'C004', 88),	('1002', 'C004', 89),
	('1003', 'C004', 90),	('1004', 'C004', 91),	('1005', 'C004', 92),	('1006', 'C004', 93),	('1001', 'C005', 77),
	('1002', 'C005', 78),	('1003', 'C005', 79);

case when練習(xí)

有如下表結(jié)構(gòu)，統(tǒng)計2019-10-21 00:00:00~2019-12-02 23:59:59時間段內(nèi)的用戶并標記新老用戶

CREATE TABLE `tb_hotel_user`  (
  `customer_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '旅客id',
  `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `user_id` bigint(10) NULL DEFAULT NULL COMMENT '用戶id',
  `check_in_time` datetime(0) NULL DEFAULT NULL COMMENT '入住時間',
  `check_out_time` datetime(0) NULL DEFAULT NULL COMMENT '離店時間',
  PRIMARY KEY (`customer_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

INSERT INTO `tb_hotel_user` VALUES (1, '張三', 1, '2019-12-02 14:18:57', NULL);
INSERT INTO `tb_hotel_user` VALUES (2, '劉大', 2, '2019-11-08 14:19:07', NULL);
INSERT INTO `tb_hotel_user` VALUES (3, '關(guān)二', 3, '2019-10-17 14:19:21', NULL);
INSERT INTO `tb_hotel_user` VALUES (4, '關(guān)二', 3, '2019-12-02 14:19:44', NULL);
INSERT INTO `tb_hotel_user` VALUES (5, '趙四', 4, '2019-11-29 14:19:54', NULL);


-- 答案
SELECT
	a.user_id,
CASE
	
	WHEN ISNULL( b.user_id ) THEN
	1 ELSE 2 
	END newUser 
FROM
	( SELECT DISTINCT user_id FROM tb_hotel_user WHERE check_in_time >= '2019-10-21 00:00:00' AND check_in_time <= '2019-12-02 23:59:59' ) a
LEFT JOIN ( SELECT user_id FROM tb_hotel_user WHERE check_in_time <= '2019-12-02 23:59:59' GROUP BY user_id HAVING count( * ) > 1 ) b ON a.user_id = b.user_id