mysql - MySQL:mysqli_fetch_array()

Question

This question already has answers here:

multiple query same table but in different columns mysql
                                
                                    （4个答案）
                                
                        
                                2年前关闭。
            
                    
如何对同一表中的这3个查询使用 ？或如何使这3个查询变成1个查询？

SELECT clinic,AVG(rating) FROM review GROUP BY clinic ORDER BY clinic
SELECT clinic,COUNT(rating) AS die FROM review WHERE rating = 1 GROUP BY clinic
SELECT clinic,COUNT(rating) AS normal FROM review WHERE rating = 7 GROUP BY clinic

最佳答案

您可以使用case表达式将最后两个查询中的条件移动到被计数的表达式。 count跳过null，所以只使用一个表达式，该表达式只返回要计算的条件的值：

SELECT   clinic, 
         AVG(rating),
         COUNT(CASE rating WHEN 1 THEN 1 END) AS die,
         COUNT(CASE rating WHEN 7 THEN 1 END) AS normal
FROM     review
GROUP BY clinic 
ORDER BY clinic

关于mysql - MySQL:mysqli_fetch_array()，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/47948421/

上一篇：php - Laravel/Eloquent 查询效率: Should a separate table be used for these two columns?

下一篇：php - 如何在 SQL 中将值显示为 Y 或 N？

Answer 1

您可以使用case表达式将最后两个查询中的条件移动到被计数的表达式。 count跳过null，所以只使用一个表达式，该表达式只返回要计算的条件的值：

SELECT   clinic, 
         AVG(rating),
         COUNT(CASE rating WHEN 1 THEN 1 END) AS die,
         COUNT(CASE rating WHEN 7 THEN 1 END) AS normal
FROM     review
GROUP BY clinic 
ORDER BY clinic