如何用SQL获取按Capacity排序的前3条记录且含至少一种异性（禁用PARTITION BY）

问题背景 #

现有Users表的结构和数据如下：

我们需要编写SQL实现以下目标：

• 按Capacity降序排序后取前3条记录
• 结果中至少包含两种性别（不能全为同一性别）
• 不能使用PARTITION BY子句
• 最终期望得到的结果如下：

解决方案 #

这里给你两种实用的实现思路，都能满足需求：

方法一：组合高容量同性别记录+最高容量异性记录

这个方法逻辑直观，完全不用窗口函数，完美避开PARTITION BY限制：

(SELECT id, Capacity, Gender
 FROM Users
 WHERE Gender = 'M'
 ORDER BY Capacity DESC
 LIMIT 2)
UNION ALL
(SELECT id, Capacity, Gender
 FROM Users
 WHERE Gender = 'F'
 ORDER BY Capacity DESC
 LIMIT 1)
ORDER BY Capacity DESC;

逻辑解释：原表中前3条按容量降序的记录全是男性，不符合要求。所以我们取前2条最高容量的男性记录，再搭配1条最高容量的女性记录，合并后重新排序，正好得到符合要求的结果——既保证了整体容量尽可能高，又满足了性别多样性。

方法二：用窗口函数标记异性存在（适用于支持窗口函数的SQL方言）

如果你的数据库支持窗口函数但不能用PARTITION BY，可以用这个方法：

SELECT id, Capacity, Gender
FROM (
    SELECT 
        id, 
        Capacity, 
        Gender,
        -- 标记当前及之前的行中是否存在不同性别的记录
        MAX(CASE WHEN Gender != (SELECT Gender FROM Users ORDER BY Capacity DESC LIMIT 1) THEN 1 ELSE 0 END) 
        OVER (ORDER BY Capacity DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS has_other_gender
    FROM Users
    ORDER BY Capacity DESC
) AS ranked
WHERE has_other_gender = 1
LIMIT 3;

逻辑解释：先给每条记录标记前面是否出现过不同性别的数据，然后筛选出已经包含异性的行，取前3条即可。

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