如何用SQL查询数据库中的顶级大家庭与男孩数量最多的家庭

嘿，我来帮你搞定这两个查询需求～先提个关键细节：你的列名里带有-连字符，SQL解析时会把它当作减法运算符，所以必须用**反引号（MySQL）或者双引号（标准SQL/PostgreSQL）**把这类列名包裹起来，不然会触发语法错误。另外你原来的子查询其实是多余的，直接分组统计就能达到目的，下面针对两个需求分别给出实现方案：

1. 查询顶级大家庭（显示父亲名、姓氏、子女数量） #

这里的「顶级」指子女数量最多的家庭，包括并列第一的所有家庭。提供两种实现方式：

方法1：子查询筛选最大数量（兼容低版本MySQL） #

SELECT 
  `name-of_father`, 
  `last-name`, 
  COUNT(*) AS 子女数量
FROM tab
GROUP BY `name-of_father`, `last-name`
HAVING COUNT(*) = (
  -- 先统计所有家庭的子女数，再找出最大值
  SELECT MAX(child_count)
  FROM (
    SELECT COUNT(*) AS child_count
    FROM tab
    GROUP BY `name-of_father`, `last-name`
  ) AS temp
);

方法2：窗口函数（MySQL 8.0+ / PostgreSQL等支持窗口函数的数据库） #

用RANK()窗口函数直接给家庭按子女数排名，取排名第一的结果，写法更简洁：

SELECT 
  `name-of_father`, 
  `last-name`, 
  子女数量
FROM (
  SELECT 
    `name-of_father`, 
    `last-name`, 
    COUNT(*) AS 子女数量,
    RANK() OVER(ORDER BY COUNT(*) DESC) AS 家庭排名
  FROM tab
  GROUP BY `name-of_father`, `last-name`
) AS 家庭排名表
WHERE 家庭排名 = 1;

2. 查询男孩数量最多的家庭 #

假设你的sex列中男孩的标识是'男'（如果是'M'或其他值，替换成实际值即可），同样提供两种实现：

方法1：子查询筛选最大男孩数 #

SELECT 
  `name-of_father`, 
  `last-name`, 
  COUNT(*) AS 男孩数量
FROM tab
WHERE sex = '男' -- 替换为你的男孩标识值
GROUP BY `name-of_father`, `last-name`
HAVING COUNT(*) = (
  SELECT MAX(boy_count)
  FROM (
    SELECT COUNT(*) AS boy_count
    FROM tab
    WHERE sex = '男'
    GROUP BY `name-of_father`, `last-name`
  ) AS temp
);

方法2：窗口函数实现 #

SELECT 
  `name-of_father`, 
  `last-name`, 
  男孩数量
FROM (
  SELECT 
    `name-of_father`, 
    `last-name`, 
    COUNT(*) AS 男孩数量,
    RANK() OVER(ORDER BY COUNT(*) DESC) AS 家庭排名
  FROM tab
  WHERE sex = '男' -- 替换为你的男孩标识值
  GROUP BY `name-of_father`, `last-name`
) AS 男孩数量排名表
WHERE 家庭排名 = 1;

额外注意事项 #

• 列名的包裹：如果你的数据库是PostgreSQL，把反引号换成双引号即可；
• 窗口函数需要数据库支持（比如MySQL 8.0及以上版本），如果是老版本数据库，优先用子查询的方法；
• 如果sex列的男孩标识不是'男'，记得替换成实际值（比如'M'、'male'等）。

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