我如何（或可以）在多个列上选择 DISTINCT？

Question

我需要从表中检索所有行，其中两列的组合都不同。 因此，我希望同一天没有任何其他销售的所有销售都以相同的价格进行。 基于日期和价格的唯一销售将更新为活动状态。

所以我在想：

UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
             FROM sales
             HAVING count = 1)

但如果再往前走，我的大脑就会受伤。

Answer 1

SELECT DISTINCT a,b,c FROM t

大致相当于：

SELECT a,b,c FROM t GROUP BY a,b,c

习惯 GROUP BY 语法是个好主意，因为它更强大。

对于您的查询，我会这样做：

UPDATE sales
SET status='ACTIVE'
WHERE id IN
(
    SELECT id
    FROM sales S
    INNER JOIN
    (
        SELECT saleprice, saledate
        FROM sales
        GROUP BY saleprice, saledate
        HAVING COUNT(*) = 1 
    ) T
    ON S.saleprice=T.saleprice AND s.saledate=T.saledate
 )

Answer 2

如果您将到目前为止的答案放在一起，进行清理和改进，您将得到这个高级查询：

UPDATE sales
SET    status = 'ACTIVE'
WHERE  (saleprice, saledate) IN (
    SELECT saleprice, saledate
    FROM   sales
    GROUP  BY saleprice, saledate
    HAVING count(*) = 1 
    );

这比它们中的任何一个都快得多。 将当前接受的答案的性能提高 10 - 15 倍（在我对 PostgreSQL 8.4 和 9.1 的测试中）。

但这还远非最佳。 使用 NOT EXISTS（反）半连接以获得更好的性能。 EXISTS 是标准 SQL，一直存在（至少从 PostgreSQL 7.2 开始，早在这个问题被提出之前）并且完全符合所提出的要求：

UPDATE sales s
SET    status = 'ACTIVE'
WHERE  NOT EXISTS (
   SELECT FROM sales s1                     -- SELECT list can be empty for EXISTS
   WHERE  s.saleprice = s1.saleprice
   AND    s.saledate  = s1.saledate
   AND    s.id <> s1.id                     -- except for row itself
   )
AND    s.status IS DISTINCT FROM 'ACTIVE';  -- avoid empty updates. see below

db<>fiddle 此处
_旧sqlfiddle

用于标识行的唯一键

（如果您不这样做）具有表的主键或唯一键（示例中的 id），您可以出于此查询的目的用系统列 ctid 替换（但不能用于其他某些查询）目的）：

   AND    s1.ctid <> s.ctid

_{每个表都应该有一个主键。 如果您还没有，请添加一个。 我建议在 Postgres 10+ 中使用 serial 或 IDENTITY 列。}

相关：

• 按顺序生成序列
• 自动递增表格列

这有多快？

一旦找到第一个重复项，EXISTS 反半连接中的子查询就可以停止计算（没有必要进一步查找）。 对于具有很少重复项的基表，这只是稍微更有效。 对于大量重复项，这会变得更加高效。

排除空更新

对于已经具有 status = 'ACTIVE' 的行，此更新不会更改任何内容，但仍以全部成本插入新的行版本（存在较小的例外情况）。 通常，您不希望这样。 添加另一个像上面演示的 WHERE 条件以避免这种情况并使其更快：

如果 status 定义为 NOT NULL，您可以简化为

AND status <> 'ACTIVE';

：列的数据类型必须支持 <> 运算符。 有些类型（例如 json）则不然。 请参阅：

• 如何查询 json 列是否为空对象？

NULL 处理中的细微差别

此查询（与 Joel 当前接受的答案不同）不处理 NULL值相等。 以下两行 (saleprice, saledate) 将被视为“不同”（尽管与人眼看起来相同）：

(123, NULL)
(123, NULL)

还传递唯一索引以及几乎任何其他位置，因为 NULL 值不进行比较根据 SQL 标准，相等。 请参阅：

• 使用空列创建唯一约束

OTOH，GROUP BY、DISTINCT 或 DISTINCT ON () 将 NULL 值视为相等。 根据您想要实现的目标，使用适当的查询样式。 您仍然可以通过 使用此更快的查询对于任何或所有比较，使用 IS NOT DISTINCT FROM 代替 = 以使 NULL 比较相等。 更多：

• 如何删除没有唯一标识符的重复行

如果所有被比较的列都定义为NOT NULL，则没有分歧的余地。

Answer 3

您的查询的问题在于，当使用 GROUP BY 子句（本质上是通过使用不同的）时，您只能使用分组依据或聚合函数的列。 您不能使用列 ID，因为可能存在不同的值。 在您的情况下，由于 HAVING 子句，始终只有一个值，但大多数 RDBMS 不够智能，无法识别这一点。

不过，这应该可行（并且不需要联接）：

UPDATE sales
SET status='ACTIVE'
WHERE id IN (
  SELECT MIN(id) FROM sales
  GROUP BY saleprice, saledate
  HAVING COUNT(id) = 1
)

您还可以使用 MAX 或 AVG 而不是 MIN，只有在只有一个匹配行时才使用返回列值的函数才重要。

Answer 4

如果您的 DBMS 不支持多列的不同，如下所示：

select distinct(col1, col2) from table

多重选择通常可以安全地执行，如下所示：

select distinct * from (select col1, col2 from table ) as x

因为这可以在大多数 DBMS 上工作，并且预计这比分组解决方案更快，因为您避免了分组功能。

Answer 5

我想从“GrondOfLucht”一列中选择不同的值，但它们应该按照“排序”列中给出的顺序进行排序。 我无法使用它仅获得一列的不同值，

Select distinct GrondOfLucht,sortering
from CorWijzeVanAanleg
order by sortering

它还会给出“排序”列，并且因为“GrondOfLucht”和“排序”不唯一，所以结果将是所有行。

使用 GROUP 按照 'sorting 给出的顺序选择 'GrondOfLucht' 的记录

SELECT        GrondOfLucht
FROM            dbo.CorWijzeVanAanleg
GROUP BY GrondOfLucht, sortering
ORDER BY MIN(sortering)