如何在 MySQL CASE 语句中使用列数据？

要理解它，请考虑“学生”表中的数据，如下所示 –

mysql> Select * from Students;
+----+-----------+-----------+----------+----------------+
| id | Name      | Country   | Language | Course         |
+----+-----------+-----------+----------+----------------+
| 1  | Francis   | UK        | English  | Literature     |
| 2  | Rick      | USA       | English  | History        |
| 3  | Correy    | USA       | English  | Computers      |
| 4  | Shane     | France    | French   | Computers      |
| 5  | Validimir | Russia    | Russian  | Computers      |
| 6  | Steve     | Australia | English  | Geoinformatics |
| 7  | Rahul     | India     | Hindi    | Yoga           |
| 8  | Harshit   | India     | Hindi    | Computers      |
| 9  | Harry     | NZ        | English  | Electronics    |
+----+-----------+-----------+----------+----------------+
9 rows in set (0.00 sec)

现在，假设我们想知道有多少学生属于美国、英国、新西兰、印度、俄罗斯、法国等，那么我们可以在 CASE 语句中使用“国家/地区”列，如下所示-

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
-> From Students;
+------+------+-------+--------+--------+------+-----------+
| USA  | UK   | INDIA | Russia | France | NZ   | Australia |
+------+------+-------+--------+--------+------+-----------+
| 2    | 1    | 2     | 1      | 1      | 1    | 1         |
+------+------+-------+--------+--------+------+-----------+
1 row in set (0.07 sec)