SQLite3按多字段优先级取分组最大记录的查询问题

错误写法原因说明 #

你之前的写法存在三个问题：

• SQLite内置的MAX()聚合函数仅支持单个参数，无法传入两个字段直接对比
• 直接使用GROUP BY name时，非聚合、非分组字段返回的是分组内的随机行，无法保证拿到符合优先级条件的id
• HAVING子句后需要跟布尔判断条件，MAX(num1, num2)返回的是数值，不满足条件语法要求

推荐实现方案 #

方案1：窗口函数实现（SQLite 3.25.0及以上版本支持，Python3.8内置的sqlite3版本普遍满足要求） #

SELECT id 
FROM (
    SELECT 
        id,
        -- 按name分组，组内先按num1倒序，再按num2倒序，给每行标序号
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY num1 DESC, num2 DESC) AS rank_num
    FROM nums
) temp
WHERE rank_num = 1;

执行后返回结果和你预期的2、4、5完全一致。

方案2：子查询兼容实现（适配所有SQLite版本） #

如果你的运行环境sqlite3版本过低不支持窗口函数，可以用NOT EXISTS子查询实现相同逻辑：

SELECT n1.id
FROM nums n1
WHERE NOT EXISTS (
    SELECT 1 
    FROM nums n2 
    WHERE n2.name = n1.name 
    AND (n2.num1 > n1.num1 OR (n2.num1 = n1.num1 AND n2.num2 > n1.num2))
);

逻辑说明：筛选出同name分组下，不存在其他行的num1更大，或是num1相同但num2更大的行，即为符合要求的目标行。

Python 调用示例 #

你可以直接用Python的sqlite3模块执行上述SQL：

import sqlite3

conn = sqlite3.connect('你的数据库文件路径.db')
cursor = conn.cursor()
# 执行上面的SQL语句
cursor.execute("""
SELECT id 
FROM (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY num1 DESC, num2 DESC) AS rank_num
    FROM nums
) temp
WHERE rank_num = 1;
""")
result = [row[0] for row in cursor.fetchall()]
print(result) # 输出 [2,4,5]
conn.close()

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