SQL每日一题（5）

前言：五更！五更琉璃！不对！是，五更佩可！

原始数据：

new_hires

题目一：

查询新进类型中，不同原因的合计人数以及人数排名。 结果输出原因、人数、排名。

题目二：

排名第 4 的原因是什么？对应人数为多少？ 填写示例：社会招聘 20

题目一：思路：排名当然dense_rank排序了，然后其余直接查询就好

SELECT
reason,
COUNT(*) AS num_people,
DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rk
FROM new_hires
GROUP BY reason
ORDER BY rk;

题目二：搜索对应rank =4的即可

WITH t1 AS (
SELECT
reason,
COUNT(*) AS num_people,
DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rk
FROM new_hires
GROUP BY reason
)
SELECT
reason,
num_people
FROM t1
WHERE rk = 4;

或者利用order by desc limit offset即可

SELECT
reason,
COUNT(*) AS num_people
FROM new_hires
GROUP BY reason
ORDER BY num_people DESC — 降序排列，人数最多的在前
LIMIT 1 OFFSET 3; — 跳过前3名，取第4名