SQLite：使用 GROUP BY 检索多列的唯一组合及关联数据

理解问题：检索多列的唯一组合

在数据库操作中，有时我们需要从表中提取特定列的唯一组合，并且对于每个这样的唯一组合，只选择一次与其关联的其他列数据。例如，在一个学生信息表中，我们可能希望获取所有唯一的 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
);

用户尝试使用 SELECT admission_number, password, DISTINCT(branch, year, section, p1_p2) FROM users; 这样的语法，但这种用法在 SQL 中是错误的。DISTINCT 关键字通常应用于 SELECT 语句的所有列，确保返回的每一行都是唯一的，而不是针对特定列组。如果需要基于一组列来识别唯一组合，并在此基础上选择其他列，正确的做法是使用 GROUP BY 子句。

解决方案：利用 GROUP BY 子句

GROUP BY 子句用于将具有相同值的行分组到汇总行中。当与聚合函数（如 MIN(), MAX(), COUNT(), SUM(), AVG() 等）结合使用时，它能对每个组执行计算。这正是解决上述问题的关键。

要实现“选择 branch, section, year, p1_p2 的唯一组合，并为每个组合选择一个 admission_number 和 password”，我们可以将这四列作为 GROUP BY 的条件。对于 admission_number 和 password，我们需要使用聚合函数来选择每个组中的一个值。

核心 SQL 查询

以下是实现目标的核心 SQL 查询：

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

查询解析

1. GROUP BY branch, section, year, p1_p2: 这指示数据库将所有具有相同 branch, section, year, p1_p2 值的行视为一个组。
2. SELECT branch, section, year, p1_p2: 这些是用于分组的列，它们将直接显示在结果中，每个组对应一行。
3. MIN(admission_number) AS admission_number, MIN(password) AS password:
   • 当使用 GROUP BY 时，SELECT 列表中除了 GROUP BY 的列之外，其他列必须是聚合函数的结果。
   • MIN() 是一个聚合函数，它会从每个组中选择指定列的最小值。在这里，它用于从每个唯一的 (branch, section, year, p1_p2) 组合中，选择一个 admission_number 和一个 password。
   • AS admission_number 和 AS password 是别名，使结果列的名称保持清晰。

通过这种方式，对于每一个独特的 (branch, section, year, p1_p2) 组合，查询将返回一行结果，其中包含该组合的 branch, section, year, p1_p2 值，以及从该组中选取的 admission_number 和 password 值。

序列猴子开放平台

具有长序列、多模态、单模型、大数据等特点的超大规模语言模型

0

查看详情

注意事项与进阶考量

1. 聚合函数的选择（MIN()/MAX() 的行为）:

   • 使用 MIN() 或 MAX() 来选择 admission_number 和 password 意味着，如果同一个 (branch, section, year, p1_p2) 组合下存在多条记录，它将分别选择 admission_number 和 password 的最小值（或最大值）。
   • 重要提示：MIN(admission_number) 和 MIN(password) 选取的这两个值不一定来自原始表中的同一行。例如，如果一个组中有两行：
     • 行A: adm_no='Z', pwd='123'
     • 行B: adm_no='A', pwd='456'MIN(admission_number) 会选择 'A'，而 MIN(password) 会选择 '123'。最终结果可能是 ('A', '123')，但原始表中可能没有 ('A', '123') 这一行。
   • 如果你的需求是“对于每个唯一组合，选择任意一个 admission_number 和 password”，那么 MIN() 或 MAX() 是完全合适的。
   • 如果你的需求是“对于每个唯一组合，选择特定一行的 admission_number 和 password”（例如，选择该组合中 id 最小的那一行），那么你需要更复杂的查询，例如使用窗口函数（SQLite 3.25.0 及更高版本支持 ROW_NUMBER()）或子查询与 JOIN。

2. 性能考虑:

   • GROUP BY 操作通常需要对数据进行排序，这在大型表上可能会影响性能。
   • 为 GROUP BY 中使用的列（branch, section, year, p1_p2）创建复合索引可以显著提高查询性能。例如：CREATE INDEX idx_users_unique_combo ON users (branch, section, year, p1_p2);

3. 与 Python 逻辑的对比: 用户提供的 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

   登录后复制

   这个 Python 逻辑隐含的含义是，对于每个首次出现的 (branch, section, year, p1_p2) 组合，它会返回当前循环到的 admission_number 和 password。SQL 的 GROUP BY 结合 MIN()/MAX() 实现了类似的功能，但选择的是组内最小值/最大值，而非“首次出现”的值。如果“首次出现”是基于 id 或其他排序，则需要更精确的 SQL 语句。但就“每个唯一组合只返回一次”而言，GROUP BY 是标准且高效的 SQL 解决方案。

总结

当需要从数据库中提取多列的唯一组合，并为每个组合关联其他列数据时，GROUP BY 子句是 SQL 中的标准且强大的工具。通过将目标唯一组合的列作为 GROUP BY 的参数，并对其他需要选择的列应用聚合函数（如 MIN() 或 MAX()），可以有效地实现这一目标。理解聚合函数在分组上下文中的行为至关重要，特别是它们如何从每个组中选取值，这可能与直觉略有不同。在处理大量数据时，为 GROUP BY 列创建索引是优化查询性能的关键一步。

以上就是SQLite：使用 GROUP BY 检索多列的唯一组合及关联数据的详细内容，更多请关注php中文网其它相关文章！