我（或者说可以）如何在多列上使用SELECT DISTINCT？

Question

我需要从一个表中检索出所有行，其中两个列的组合是完全不同的。因此，我希望得到所有销售记录，这些记录在同一天以相同的价格没有其他销售记录。基于日期和价格独特的销售记录将被更新为活动状态。

所以我想:

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

但是我继续深入思考会感到头疼。

- sheats

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
 )

- Joel Coehoorn

---

这个查询语句虽然正确且已经被接受了一年，但非常低效，而且没有必要。不要使用它。我在另一个答案中提供了一种替代方法和一些解释。 - Erwin Brandstetter

"SELECT DISTINCT a,b,c FROM t"难道不就是和"SELECT a,b,c FROM t GROUP BY a,b,c"一模一样的吗？ - famargar

@famargar，尽管对于简单的情况两者的含义相同，但从语义上来说它们有不同的含义，而在构建更大查询的步骤时可以采取不同的方法。此外，技术论坛上的人们经常会对事物极为追求严谨，因此在我的帖子中加入模棱两可的措辞通常是有用的。 - Joel Coehoorn

Answer 2

如果您整合目前的答案并进行清理和改进，您将得到以下更好的查询：

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

这比它们中的任何一个都要快得多。在我的PostgreSQL 8.4和9.1测试中，性能因子为10-15而超过了当前接受的答案。

但是这仍然远非最佳选择。使用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)

同时传递一个唯一的索引，几乎可以在任何地方使用，因为根据SQL标准，NULL值不相等。参见：

• 使用null列创建唯一约束

另一方面，GROUP BY、DISTINCT或DISTINCT ON ()将NULL值视为相等。根据您想要实现的目标使用适当的查询样式。仍然可以使用IS NOT DISTINCT FROM代替=进行任何或所有比较，使NULL比较相等，从而使用更快的查询。更多信息：

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

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

- Erwin Brandstetter

---

好的回答。我是一名SQL Server专家，所以使用包含IN()检查的元组的第一个建议并没有想到我。在SQL Server中，使用NOT EXISTS建议通常会得到与内连接相同的执行计划。 - Joel Coehoorn

好的。该解释大大增加了答案的价值。我几乎想用Oracle运行一些测试，以查看计划与Postgres和SQLServer相比如何。 - Peter

@alairock：你从哪里得到这个信息的？对于Postgres来说，情况恰恰相反。在计算所有行时，“count（*）”比“count（<expression>）”更有效率。你可以试试。Postgres对该聚合函数变体有更快的实现方式。也许您混淆了Postgres和其他某些RDBMS？ - Erwin Brandstetter

@alairock：我碰巧是那个页面的共同作者，它并没有说那样的话。 - Erwin Brandstetter

@ErwinBrandstetter，你在各个平台上的回答总是非常准确。多年来，你以一种难以想象的方式提供了帮助。对于这个例子，我已经知道了几种不同的解决方法，但我想看看是否有人测试了各种可能性之间的效率差异。谢谢。 - WebWanderer

Answer 3

你的查询存在问题，因为使用GROUP BY子句（实际上是通过使用DISTINCT）时，只能使用按组合并或聚合函数计算出来的列。你不能使用id列，因为它可能有不同的值。虽然在你的情况下仅有一个值，因为HAVING子句的原因，但大多数关系数据库管理系统无法识别这一点。

然而，以下查询应该可以工作（且不需要JOIN）：

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

如果只有一行匹配，重要的是使用返回列值的函数，而不是必须使用MIN函数，可以使用MAX或AVG代替。

- Christian Berg

Answer 4

如果您的数据库管理系统不支持像这样多列去重：

select distinct(col1, col2) from table

通常情况下，多选可以按照以下安全方式执行：

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

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

- Abdulhafeth Sartawi

---

你仍然有重复项。在我的情况下，我有列（id，col_a）。列col_a具有重复项，我想使用DISTINCT来删除重复项。使用代码SELECT DISTINCT(id，col_a) FROM Table将导致： “（2,2）” “（3,3）” “（4,3）” “（5,4）”如您所见，第二列存在重复项。我正在寻找解决方案。 - AER 4AF

我找到了这个解决方案： SELECT DISTINCT ON (col_a) id,col_a FROM Table; - AER 4AF

Answer 5

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

Select distinct GrondOfLucht,sortering
from CorWijzeVanAanleg
order by sortering

此外，它还将提供“排序”列，因为“GrondOfLucht”和“排序”并非唯一，结果将是所有行。

使用GROUP选择按'sortering'给定的顺序筛选'GrondOfLucht'记录。

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

- frans eilering

---

这基本上解释了被接受的答案所做的事情，但我建议不要使用这样的名称作为示例（至少翻译它们）。附注：我建议在所有项目中始终使用英语命名所有内容，即使您是荷兰人。 - Kerwin Sneijders