我如何(或我可以)选择不同的多列?

我需要从一个表中检索所有行，其中2列组合都是不同的。所以我想要所有在同一天以相同价格没有其他销售的销售。基于日期和价格的独特销售将被更新为活跃状态。

所以我在想:

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

但再往前走我的脑袋就疼了。

1117079 次浏览
小开
最佳答案 
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
)
小开

查询的问题是，当使用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，只有在只有一个匹配行的情况下，使用返回列值的函数才重要。

小开

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

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
< p > Db<>fiddle here .
老sqlfiddle子> < /

标识行的唯一键

如果你没有表的主键或唯一键(在例子中是id)，你可以用system列ctid来代替这个查询(但不是为了其他目的):

   AND    s1.ctid <> s.ctid

每个表都应该有一个主键。如果你还没有，就加一个。我建议使用Postgres 10+中的serial或IDENTITY列。

相关:

这怎么更快?

EXISTS反半连接中的子查询可以在发现第一个欺骗时立即停止求值(没有必要进一步查找)。对于副本很少的基表来说，这只是稍微提高了一些效率。有了大量的重复，道路变得更有效。

排除空更新

对于已经有status = 'ACTIVE'的行，这个更新不会改变任何东西，但仍然会以全部成本插入一个新的行版本(轻微例外适用)。通常情况下，你不希望这样。添加另一个WHERE条件，如上面所示，以避免这种情况，并使其更快:

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

AND status <> 'ACTIVE';

列的数据类型必须支持<>操作符。一些类型，如json，则没有。看到的:

NULL处理的细微差别

此查询(与目前接受乔尔的回答不同)不将NULL值视为相等。(saleprice, saledate)的下面两行将符合“;distinct”;(虽然看起来和人眼一模一样):

(123, NULL)
(123, NULL)

还传入唯一索引和几乎任何其他位置，因为根据SQL标准，NULL值比较不相等。看到的:

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

如果所有被比较的列都定义为NOT NULL，则没有不一致的空间。

小开

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

Select distinct GrondOfLucht,sortering
from CorWijzeVanAanleg
order by sortering

它还会给出列'sortering'，因为'GrondOfLucht' and 'sortering'不是唯一的，结果将是ALL行。

使用GROUP按“sortering”给出的顺序选择“grundoflucht”的记录

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

如果你的DBMS不支持这样的多列:

select distinct(col1, col2) from table

多选择一般可以安全执行如下:

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

因为这可以在大多数DBMS上工作，而且这比分组解决方案要快，因为你避免了分组功能。


提交答案