MySQL聚合函数如何与MySQL IF()函数结合？

Combining MySQL aggregate functions with MySQL IF() function can be very helpful to get the specific output we want. Consider the following queries which combine SUM() and COUNT() aggregate functions with IF() function.

Example

mysql> Select SUM(IF(Language = 'English', 1, 0)) As English, SUM(IF(Language <> 'English',1,0)) AS "Non-English" from Students;
+---------+-------------+
| English | Non-English |
+---------+-------------+
| 5       | 4           |
+---------+-------------+
1 row in set (0.00 sec)

上述查询将SUM()聚合函数与IF()函数结合使用，从“学生”表中获取英语母语学生和非英语母语学生的输出。

mysql> Select COUNT(IF(country = 'USA', 1, NULL))AS USA,
    -> COUNT(IF(country = 'UK', 1, NULL))AS UK,
    -> COUNT(IF(country = 'France', 1, NULL))AS France,
    -> COUNT(IF(country = 'Russia', 1, NULL))AS Russia,
    -> COUNT(IF(country = 'Australia', 1, NULL))AS Australia,
    -> COUNT(IF(country = 'INDIA', 1, NULL))AS INDIA,
    -> COUNT(IF(country = 'NZ', 1, NULL))AS NZ FROM Students;
+-----+----+--------+--------+-----------+-------+----+
| USA | UK | France | Russia | Australia | INDIA | NZ |
+-----+----+--------+--------+-----------+-------+----+
| 2   | 1  | 1      | 1      | 1         | 2     | 1  |
+-----+----+--------+--------+-----------+-------+----+
1 row in set (0.07 sec)

上面的查询将COUNT()聚合函数与IF()函数结合起来，以从“Students”表中获取国家数量的输出。