SQLite查询：如何从每个家庭选取18岁及以上的最年轻成员？

我使用SQLite数据库，有一张名为people的表，存储人员姓名等信息，部分人员属于同一家庭。表结构及数据如下：

'-----------------------------------'
| Id | Last Name | First Name | Age |
------------------------------------|
| 1  | Gordon    | James      |  5  |
| 2  | Gordon    | Mike       | 19  |
| 3  | Gordon    | Sara       |  8  |
| 4  | Gordon    | Cludia     | 25  |
| 5  | Sagget    | Bob        | 22  |
| 6  | Saywer    | Tom        |  9  |
| 7  | Saywer    | Jean       | 20  |
| 8  | Finn      | Hucklberry |  8  |
| 9  | Smith     | John       | 18  |
| 10 | Smith     | Sue        | 39  |
'-----------------------------------'

需求是：查询所有年龄≥18岁的人员，但每个家庭仅选取一名成员，且必须是该家庭中年龄≥18岁的最年轻成员。预期查询结果如下：

'-----------------------------------'
| Id | Last Name | First Name | Age |
------------------------------------|
| 2  | Gordon    | Mike       | 19  |
| 5  | Sagget    | Bob        | 22  |
| 7  | Saywer    | Jean       | 20  |
| 9  | Smith     | John       | 18  |
'-----------------------------------'

注：Hucklberry Finn因年龄不足18岁且无符合条件的亲属，未出现在结果中。

我尝试了以下SQL语句，但认为存在更正确高效的实现方式，请求最优查询方案：

SELECT
    id,
    last_name,
    first_name,
    age
FROM
    people p
WHERE
    age >= 18
    and age < (
      select min(age) from people where age > 18 and last_name = p.last_name
)

原语句的问题 #

原查询逻辑存在错误：当家庭中符合年龄条件的最年轻成员是18岁时，子查询select min(age) from people where age > 18 and last_name = p.last_name会返回NULL，而age < NULL的判断结果为UNKNOWN，导致该成员被过滤（比如Smith家的John就无法被查询到），不符合预期需求。

最优查询方案 #

方案1：使用窗口函数（SQLite 3.25+支持）

这是最简洁高效的方式，利用窗口函数按家庭分组并排序，直接筛选出每组的第一条记录：

SELECT id, last_name, first_name, age
FROM (
    SELECT 
        id, 
        last_name, 
        first_name, 
        age,
        -- 按家庭分组，组内按年龄升序分配行号
        ROW_NUMBER() OVER (PARTITION BY last_name ORDER BY age ASC) AS rn
    FROM people
    WHERE age >= 18
) AS ranked_people
-- 取每组行号为1的记录（即该家庭最年轻的符合条件成员）
WHERE rn = 1;

方案2：分组聚合关联查询（兼容低版本SQLite）

如果你的SQLite版本低于3.25，不支持窗口函数，可以先分组找出每个家庭符合条件的最小年龄，再关联原表获取对应记录：

SELECT p.id, p.last_name, p.first_name, p.age
FROM people p
JOIN (
    -- 分组计算每个家庭中≥18岁成员的最小年龄
    SELECT last_name, MIN(age) AS min_qualified_age
    FROM people
    WHERE age >= 18
    GROUP BY last_name
) AS family_min 
ON p.last_name = family_min.last_name AND p.age = family_min.min_qualified_age
WHERE p.age >= 18;

这两种方案都能正确返回预期结果，且性能优于原语句——原语句的子查询会逐行执行，而上述方案通过分组或窗口函数减少了重复计算。

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