在哪种情况下,MySQL CASE语句返回NULL?
我们知道,如果没有比较或条件为真,则CASE语句返回在 ELSE语句之后指定的结果 。但是,如果没有 ELSE语句,那么在这种情况下,CASE语句将返回NULL。下面是一个示例来演示它。
示例
mysql> Select CASE 100
-> WHEN 150 THEN 'It is matched'
-> WHEN 200 THEN 'It is not matched'
-> END As 'It Returns NULL';
+-----------------+
| It Returns NULL |
+-----------------+
| NULL |
+-----------------+
1 row in set (0.00 sec)下面的查询使用表'Students'中的数据,返回NULL,因为没有学生属于WI国家。
mysql> Select SUM(CASE WHEN country = 'USA' THEN 1 ELSE 0 END) AS USA,
-> SUM(CASE WHEN country = 'UK' THEN 1 ELSE 0 END) AS UK,
-> SUM(CASE WHEN country = 'INDIA' THEN 1 ELSE 0 END) AS INDIA,
-> SUM(CASE WHEN country = 'Russia' THEN 1 ELSE 0 END) AS Russia,
-> SUM(CASE WHEN country = 'France' THEN 1 ELSE 0 END) AS France,
-> SUM(CASE WHEN country = 'NZ' THEN 1 ELSE 0 END) AS NZ,
-> SUM(CASE WHEN country = 'Australia' THEN 1 ELSE 0 END) AS Australia,
-> SUM(CASE WHEN country = 'WI' THEN 1 END) AS WI
-> From Students;
+------+------+-------+--------+--------+------+-----------+------+
| USA | UK | INDIA | Russia | France | NZ | Australia | WI |
+------+------+-------+--------+--------+------+-----------+------+
| 2 | 1 | 2 | 1 | 1 | 1 | 1 | NULL |
+------+------+-------+--------+--------+------+-----------+------+
1 row in set (0.00 sec)热门推荐
1 训练狗狗憋尿
2 狗狗追踪训练
3 狗狗训练要多少钱
5 深圳狗狗训练
6 训练狗狗唤回
7 狗狗叫怎么训练
8 狗狗训练班
10 狗狗社会化训练
11 狗狗叫回训练
12 广州狗狗训练学校
13 狗狗训练躺下
14 狗狗随行训练
15 训练狗狗巡回
16 狗狗要怎么训练
17 狗狗喂食训练
18 训练狗狗躺下