【MySQL】ALL函数的巧用 以及 排序（order by）巧用 sum(条件表达式) 语法

力扣题

1、题目地址

578. 查询回答率最高的问题

2、模拟表

SurveyLog 表：

Column Name	Type
id	int
action	ENUM
question_id	int
answer_id	int
q_num	int
timestamp	int

• 这张表可能包含重复项。
• action 是一个 ENUM(category) 数据，可以是 “show”、“answer” 或者 “skip” 。
• 这张表的每一行表示：ID = id 的用户对 question_id 的问题在 timestamp 时间进行了 action 操作。
• 如果用户对应的操作是 “answer” ，answer_id 将会是对应答案的 id ，否则，值为 null 。
• q_num 是该问题在当前会话中的数字顺序。

3、要求

回答率 是指：同一问题编号中回答次数占显示次数的比率。

编写一个解决方案以报告 回答率 最高的问题。如果有多个问题具有相同的最大 回答率 ，返回 question_id 最小的那个。

查询结果如下例所示。

示例 1：

输入：
SurveyLog table:

id	action	question_id	answer_id	q_num	timestamp
5	show	285	null	1	123
5	answer	285	124124	1	124
5	show	369	null	2	125
5	skip	369	null	2	126

输出：

survey_log
285

解释：
问题 285 显示 1 次、回答 1 次。回答率为 1.0 。
问题 369 显示 1 次、回答 0 次。回答率为 0.0 。
问题 285 回答率最高。

4、代码编写

我的写法

SELECT question_id AS survey_log
FROM (
    SELECT question_id, SUM(action = 'answer')/SUM(action = 'show') AS q
    FROM SurveyLog
    GROUP BY question_id
) AS one
WHERE q >= ALL(
    SELECT SUM(action = 'answer')/SUM(action = 'show') AS q
    FROM SurveyLog
    GROUP BY question_id
)
ORDER BY question_id
LIMIT 1

代码解析：

1、先算出问题各自的回答率（sum(条件表达式) 只要里面条件表达式是 true 就加一，和 count 作用一样）

SELECT question_id, SUM(action = 'answer')/SUM(action = 'show') AS q
FROM SurveyLog
GROUP BY question_id

2、求出回答率是最高的问题

WHERE q >= ALL(
    SELECT SUM(action = 'answer')/SUM(action = 'show') AS q
    FROM SurveyLog
    GROUP BY question_id
)

ALL 函数的效果可以理解成（这是 ALL 前面是 >= 或 > 的情况，如果ALL前面是 <= 或 < 的情况，则是 MIN）

SELECT MAX(q)
FROM (
	SELECT SUM(action = 'answer')/SUM(action = 'show') AS q
    FROM SurveyLog
    GROUP BY question_id
) AS one

还有一个 ANY 函数，与 ALL 函数相反（ANY 前面是 >= 或 > 的情况，是 MIN，如果 ANY 前面是 <= 或 < 的情况，则是 MAX）

具体可参考：MySQL 中 ALL 和 ANY 的用法
参考里面注意看评论，参考里面最后一个例子看着是有误的

网友写法

SELECT question_id AS survey_log
FROM SurveyLog
GROUP BY question_id
ORDER BY SUM(action = 'answer')/SUM(action = 'show') DESC , question_id
LIMIT 1

代码解析：

1、分组已经决定了后面出来的条数，我们要取出满足条件的放在前面，然后取一条即可（LIMIT 1），这就需要用到排序。

2、排序的话，我们知道是要 回答率高 的放在前面，SUM(action = 'answer')/SUM(action = 'show') 就是算出回答率，DESC 倒序

• 后面按要求如果 最大回复率没有重复，直接取第一条即可
• 后面按要求如果 最大回答率有重复，则按 question_id 正序排后，再取第一条即可