如何将出现频次TOP3的动物生成各占一列的结果集？附示例

嘿，我来帮你搞定这个需求！要把出现频次TOP3的动物分别放在单独的列里，最优的方法是结合窗口函数排名和条件聚合，逻辑清晰还兼顾性能和兼容性，下面给你一步步拆解：

步骤1：统计动物频次并排名 #

首先我们需要先算出每个动物的出现次数，同时给它们按频次从高到低排个序（如果频次相同，可以按动物名称排序来确定先后）。用窗口函数ROW_NUMBER()就能轻松实现：

SELECT 
    animal,
    COUNT(*) AS occurrence_count,
    ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC, animal) AS rank_num
FROM your_table_name
GROUP BY animal

这里的ROW_NUMBER()会给每个动物分配唯一的排名，频次高的排前面；如果两个动物频次一样，就按animal字段的字母顺序排（比如例子里的cat和mouse频次相同，cat会排在mouse前面）。

步骤2：将排名结果转为单列展示 #

接下来我们把排名前3的动物分别放到对应的列里，用条件聚合MAX(CASE...)的方式最通用，几乎所有现代SQL数据库都支持：

SELECT
    MAX(CASE WHEN rank_num = 1 THEN animal END) AS `animal 1`,
    MAX(CASE WHEN rank_num = 2 THEN animal END) AS `animal 2`,
    MAX(CASE WHEN rank_num = 3 THEN animal END) AS `animal 3`
FROM (
    -- 这里嵌套上面的排名子查询
    SELECT 
        animal,
        ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC, animal) AS rank_num
    FROM your_table_name
    GROUP BY animal
) ranked_animals
WHERE rank_num <= 3

运行这段代码后，就能得到你想要的结果：

-- animal 1 -- animal 2 -- animal 3 --
dog cat mouse

为什么这是最优方法？ #

• 兼容性拉满：不管你用MySQL 8+、PostgreSQL、SQL Server还是Oracle，这个写法都能直接跑通，比数据库专属的PIVOT语法适用性更广
• 性能高效：只需要两次数据扫描（一次聚合排名，一次条件转列），在大数据量下也能保持不错的速度
• 易维护调整：如果要改成TOP5，只需要增加对应的CASE分支和修改WHERE条件里的数字就行，逻辑一目了然

内容的提问来源于stack exchange，提问作者Jan Trindal