数据
+----+-----------+--------------+------------+--------+------------+
| 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 |
+----+-----------+--------------+------------+--------+------------+
MYSQL-
select question_id as survey_log
from
(
SELECT sum(CASE WHEN action='answer' THEN 1 ELSE 0 END) as num,
question_id,
count(distinct id) as den
from
survey_log
group by question_id
) b
order by (num/den) desc
limit 1
输出
285
MSSQL
select top 1 question_id as survey_log
from
(
SELECT sum(CASE WHEN action='answer' THEN 1 ELSE 0 END) as num,
question_id,
count(distinct id) as den
from
survey_log
group by question_id
) b
order by (num/den) desc
输出
369
对于大多数场景,在这个问题之前,我使用top 1和limit 1来获得类似的结果。不知怎么的,在这个查询中,我得到了不同的结果。我哪里错了?MSSQL中TOP
子句的执行顺序是否不同?或者我完全混淆了两者的用例?
Leetcode 的原始问题
在SQL Server中,两个整数的除法是一个整数。因此,1/2=0,而不是0.5。
要解决此问题,请使用:
order by num * 1.0 / den
此外,如果order by
键存在重复值,则将返回任意等效行。