主页 / dba / 问题 / 320535
Accepted
Alex
Asked: 2022-12-05 05:29:42 +0800 CST

选择行，但删除具有重复列的行

  • 772

我相信这是一个非常简单的查询，但由于某种原因它在 Postgres 中不起作用（它在其他数据库中起作用）

这就是 db schema kooks 的样子：

tableA(
   id integer,
   ...
)

tableB(
   id integer
   tableA_id integer -- foreign key to tableA
   ...
)

我想为 tableB 选择记录，与 tableA 连接，然后按 tableA_id 分组：

SELECT * from tableB b INNER JOIN tableA ON b.tableA_id = tableA.id
GROUP BY tableA_id
LIMIT 20

除了 Postgres，它在任何地方都可以正常工作，它会抛出臭名昭著的“id 必须出现在 group by 中或用于聚合函数”错误。但是如果我把 b.id 放进去group by，我就不会删除重复的记录......

postgresql

2 个回答

  1. Best Answer

    请注意，您应该始终包括完整的表架构和示例数据，因为这会使您的问题更清楚。

    通常，要将不在GROUP BY子句中的字段包含在SELECT列表中，您应该使用聚合函数，如下所示：

    SELECT
    b.tableA_id,
    MAX(b.id) AS max_b_id -- Choose whichever aggregate function makes sense here
from tableB b 
INNER JOIN tableA 
    ON b.tableA_id = tableA.id
GROUP BY b.tableA_id
LIMIT 20

    这将允许您从未分组的列中返回值，并且您的GROUP BY子句仍将像以前一样对记录进行重复数据删除。请注意，您必须选择哪个聚合函数对您想要的未分组列最有意义SELECT。上面我选择了函数作为例子，在这种情况下MAX()它只会返回最大的。b.id如果你想要最低的，b.id那么使用MIN()函数等。

    在不知道您表中的其余字段或查看示例数据的情况下，我不确定您的查询目标是什么。每当您运行它时，它也会返回一个不确定的（随机的）20 行，因为您指定了一个LIMIT没有ORDER BY子句的。

    另外，请不要使用SELECT *，它是一种反模式。始终明确地仅列出查询中需要的列。这就是您收到问题错误的方式，因为*它会尝试SELECT取消分组列。

    也许您甚至不想返回该b.id列，通过显式列出列名，您甚至不需要包含它。（同样，不确定查询的目标是什么？）因此，如果是这种情况，您可以将其从我的示例查询中删除。

    • 3

  2. 我相信这是一个非常简单的查询，但由于某种原因它在 Postgres 中不起作用（它在其他数据库中起作用）

    SELECT * 
FROM tableB b INNER JOIN tableA ON b.tableA_id = tableA.id
GROUP BY tableA_id
LIMIT 20 ;

    这是不正确的。在任何已正确实施标准 SQL 的 SQL DBMS 中，该语句将无法正常工作。Postgres、SQL Server、Oracle、Firebird、DB2、event 最新版本的 MySQL 都会拒绝具有类似错误消息的语句。

    唯一的两个例外（我知道）是（旧版本的）MySQL 和 SQLite。

    当前版本的 MySQL（具有默认设置）将拒绝带有类似于 Postgres 的消息的语句。旧版本的 MySQL（5.6 及更早版本，具有默认设置）将接受该语句并返回结果，但它们可能不正确。分组列 (tableAid) 的每个值都会返回一行，但不保证这些值来自同一行。因此有可能得到表中不存在的结果！

    在 SQLite 中，情况要好一些。该语句被接受并且结果是一致的，来自相同的（每组一行）。聚合查询中的详细信息可以包含不在 GROUP BY 子句中的非聚合结果列 。一个问题当然是这种行为是非标准 SQL，您不能期望这种查询像在其他 DBMS 中那样运行。事实上，您应该预料到它们会在所有其他 DBMS 中被拒绝，并且需要更正。

    对于 Postgres，您很幸运，因为 SQLite 对这种非标准行为的实现正是 Postgres（非标准）DISTINCT ON所做的。所以具体查询将等同于以下内容。您还可以使用ORDER BY来影响每组将选择哪些行以包含在结果中：

    SELECT DISTINCT ON (tableA_id)
    * 
FROM tableB b INNER JOIN tableA ON b.tableA_id = tableA.id
-- ORDER BY tableA_id, ...
LIMIT 20 ;
    • 2

相关问题