我如何(或可以)在多列上选择DISTINCT？

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

所以我在想:

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

但是我的大脑比那更远了.

1> Joel Coehoor..：

---

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
 )

---

这个查询虽然正确并且现在被接受了一年,但是**效率非常低**并且不必要地如此.不要使用它.我在另一个答案中提供了另一种解释和一些解释.

---

@famargar对于简单的情况,但是它们在语义上有不同的含义,并且它们在构建更大的查询时可以为步骤做什么方面不同.此外,科技论坛上的人们往往对事物非常痴迷,我发现在这种情况下将狡猾的词语添加到我的帖子中通常很有用.

2> Erwin Brands..：

---

如果你把目前为止的答案放在一起,清理和改进,你会得到这个优越的问题:

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

SQL小提琴.

识别行的唯一键

如果您没有表的主键或唯一键(id在示例中),则可以使用系统列替换ctid此查询(但不是出于其他目的):

   AND    s1.ctid <> s.ctid

_{每个表都应该有一个主键.如果还没有,请添加一个.我推荐Postgres 10+中的一个serial或一个IDENTITY专栏.}

有关:

有序序列生成

自动增量表列

这怎么快？

EXISTS反半连接中的子查询可以在找到第一个欺骗时立即停止评估(没有必要进一步查看).对于具有少量重复的基表,这只是稍微更有效.随着大量重复的这成为方式更有效.

排除空更新

如果已有一些或多行status = 'ACTIVE',您的更新将不会更改任何内容,但仍会以全部成本插入新行版本(适用次要例外).通常,你不想要这个.添加WHERE上面演示的另一个条件,使其更快:

如果status已定义NOT NULL,则可以简化为:

AND status <> 'ACTIVE';

NULL处理的细微差别

此查询(与Joel当前接受的答案不同)不会将NULL值视为相等.这两行将(saleprice, saledate)符合"不同"(虽然看起来与人眼相同):

(123, NULL)
(123, NULL)

还传入一个唯一的索引,几乎在任何其他地方,因为根据SQL标准,NULL值不能相等.看到:

使用空列创建唯一约束

OTOH,GROUP BY或DISTINCT或DISTINCT ON ()将NULL值视为相等.根据您要实现的目标使用适当的查询样式.您仍然可以使用这种更快的查询样式,IS NOT DISTINCT FROM而不是使用=任何或所有比较来使NULL比较相等.更多:

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

如果定义了所有比较的列NOT NULL,则没有分歧的余地.

---

好答案.我是一个sql服务器的人,所以第一个使用带有IN()检查的元组的建议不会发生在我身上.不存在的建议通常会在sql server中以与内连接相同的执行计划结束.

---

@alairock:我碰巧是该页面的共同作者,并没有说出任何类似的内容.

---

真好 这种解释大大增加了答案的价值。我几乎想对Oracle进行一些测试，以查看该计划与Postgres和SQLServer的比较。

---

@alairock：你从哪儿得到的？对于Postgres，*相反*为true。在计算所有行时，`count（*）`比`count（<expression>）`效率更高。去尝试一下。Postgres对于聚合函数的此变体具有更快的实现。也许您将Postgres与其他RDBMS混淆了？

---

@ErwinBrandstetter，您总是会在整个堆栈中找到答案。这些年来，您以几乎无法想象的方式提供了帮助。对于此示例，我知道几种解决问题的方法，但是我想看看有人在各种可能性之间测试了效率。谢谢。

3> Christian Be..：

---

您的查询的问题是,当使用GROUP BY子句(您实际上通过使用distinct)时,您只能使用分组的列或聚合函数.您不能使用列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,如果只有一个匹配的行,则使用返回列值的函数非常重要.