如何在SQLite中按多列组合查询不重复的指定字段数据？

问题说明 #

需要从users表中筛选出branch、section、year、p1_p2的唯一组合，每个组合仅返回一次对应的admission_number和password。表结构如下：

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    admission_number TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    branch TEXT NOT NULL,
    section INTEGER NOT NULL,
    year INTEGER NOT NULL,
    p1_p2 TEXT NOT NULL
);

使用环境：aiosqlite 0.19.0 + Python 3.12

原尝试的SQL语句因语法错误无法执行：

SELECT admission_number, password, DISTINCT(branch, year, section, p1_p2)
FROM users;

期望实现的逻辑等价于以下Python代码：

seen: list[tuple] = []
for id, admission_number, password, branch, section, year, p1_p2 in users:
    if (branch, section, year, p1_p2) not in seen:
        seen.append((branch, section, year, p1_p2))
        yield admission_number, password

可行解决方案 #

方案1：使用DISTINCT ON（SQLite 3.35.0+支持） #

SQLite 3.35.0及以上版本支持DISTINCT ON语法，可直接指定按目标列去重，返回每组的第一条记录，完全匹配需求：

SELECT DISTINCT ON (branch, section, year, p1_p2)
       admission_number, password
FROM users;

若需要指定返回每组的特定行（例如最新插入的记录），可配合ORDER BY调整：

SELECT DISTINCT ON (branch, section, year, p1_p2)
       admission_number, password
FROM users
ORDER BY branch, section, year, p1_p2, id DESC; -- 按id倒序取每组最后插入的记录

方案2：分组查询（兼容旧版SQLite） #

如果你的SQLite版本低于3.35.0，可使用GROUP BY结合聚合函数实现。因admission_number是唯一字段，用MIN()或MAX()均可获取每组的对应值：

SELECT MIN(admission_number) AS admission_number, MIN(password) AS password
FROM users
GROUP BY branch, section, year, p1_p2;

方案3：窗口函数（SQLite 3.25.0+支持） #

利用ROW_NUMBER()窗口函数给每组行编号，再筛选编号为1的行：

WITH ranked_users AS (
    SELECT 
        admission_number, password,
        ROW_NUMBER() OVER (PARTITION BY branch, section, year, p1_p2 ORDER BY id) AS rn
    FROM users
)
SELECT admission_number, password
FROM ranked_users
WHERE rn = 1;

修改ORDER BY id为ORDER BY id DESC可获取每组最后插入的记录，和Python逻辑中取首次出现的记录对应则保留ORDER BY id ASC。

aiosqlite使用示例（以方案1为例） #

import aiosqlite
import asyncio

async def get_unique_user_records():
    async with aiosqlite.connect('your_db_name.db') as db:
        async with db.execute('''
            SELECT DISTINCT ON (branch, section, year, p1_p2)
                   admission_number, password
            FROM users
        ''') as cursor:
            async for admission_num, pwd in cursor:
                print(f"学号: {admission_num}, 密码: {pwd}")

asyncio.run(get_unique_user_records())

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