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”表中获取国家数量的输出。
想要了解更多内容,请持续关注码农资源网,一起探索发现编程世界的无限可能!
本站部分资源来源于网络,仅限用于学习和研究目的,请勿用于其他用途。
如有侵权请发送邮件至1943759704@qq.com删除
码农资源网 » MySQL聚合函数如何与MySQL IF()函数结合?
本站部分资源来源于网络,仅限用于学习和研究目的,请勿用于其他用途。
如有侵权请发送邮件至1943759704@qq.com删除
码农资源网 » MySQL聚合函数如何与MySQL IF()函数结合?