PostgreSQL 15：如何查询每个孩子最受欢迎的玩具类型

现有一张记录孩子与其玩具信息的kids_toys表，表结构及数据如下：

表结构 #

CREATE TABLE kids_toys (
  kid_name character varying,
  toy_type character varying,
  toy_name character varying
);

表数据 #

需求 #

按孩子分组，获取每个孩子最受欢迎的玩具类型（即该孩子拥有数量最多的玩具类型），预期结果如下：

使用PostgreSQL 15作为数据库引擎，目前卡在生成计数后如何筛选每个孩子的最大计数结果，如何编写查询语句？

在PostgreSQL中，可通过以下两种常用方式实现需求：

方案一：窗口函数筛选（推荐） #

先统计每个孩子每种玩具类型的数量，再用窗口函数按孩子分组、按计数降序排序，最后筛选排序第一的结果：

WITH toy_counts AS (
    SELECT 
        kid_name,
        toy_type,
        COUNT(*) AS count
    FROM kids_toys
    GROUP BY kid_name, toy_type
)
SELECT kid_name, toy_type, count
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY kid_name ORDER BY count DESC) AS rn
    FROM toy_counts
) ranked_data
WHERE rn = 1;

• 若同一孩子存在多种玩具类型计数并列最多的情况，ROW_NUMBER()仅会返回其中一条；如需返回所有并列记录，可替换为RANK()。
• CTE（WITH子句）先完成计数统计，简化后续排序筛选逻辑。

方案二：子查询关联筛选 #

先统计每个孩子每种玩具的数量，再通过子查询找出每个孩子的最大计数，最后关联匹配出符合条件的记录：

SELECT 
    tc.kid_name,
    tc.toy_type,
    tc.count
FROM (
    SELECT 
        kid_name,
        toy_type,
        COUNT(*) AS count
    FROM kids_toys
    GROUP BY kid_name, toy_type
) tc
JOIN (
    SELECT 
        kid_name,
        MAX(count) AS max_count
    FROM (
        SELECT 
            kid_name,
            toy_type,
            COUNT(*) AS count
        FROM kids_toys
        GROUP BY kid_name, toy_type
    ) temp_counts
    GROUP BY kid_name
) max_counts 
ON tc.kid_name = max_counts.kid_name AND tc.count = max_counts.max_count;

• 该方案会返回同一孩子所有计数并列最高的玩具类型，适合需要保留并列结果的场景。

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