所以我在想:
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 1)
但是我的大脑比这更痛。
#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
)
#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
用于标识行的唯一键
如果表没有主键或唯一键(在示例中为
id
),则可以替换为用于此查询的系统列ctid
(但不适用于其他目的): AND s1.ctid <> s.ctid
每个表都应具有主键。如果还没有,请添加一个。我建议在Postgres 10+中使用
serial
或IDENTITY
列。相关信息:
有序序列生成
自动递增表列
/>
如何更快?
一旦找到第一个重复对象,
EXISTS
反半联接中的子查询就可以停止评估(毫无意义)。对于几乎没有重复的基本表,这只会稍微提高效率。重复很多,这将变得更有效率。排除空更新
对于已经具有
status = 'ACTIVE'
的行,此更新不会更改任何内容,但仍会在处插入新的行版本全部费用(有少量例外)。通常,您不希望这样做。添加另一个如上所述的WHERE
条件来避免这种情况并使它变得更快:如果定义了
status
,则可以简化为:AND status <> 'ACTIVE';
列的数据类型必须支持
NOT NULL
运算符。诸如<>
之类的某些类型则不然。请参阅:如何查询json列中的空对象?
NULL处理中的细微差别
此查询(与Joel当前接受的答案)不会将NULL值视为相等。
json
的以下两行将被视为“与众不同”(尽管看起来与人眼相同):(123, NULL)
(123, NULL)
还传递唯一的索引,并且几乎在其他任何地方,因为根据SQL标准,NULL值不等于相等。请参阅:
使用空列创建唯一约束
OTOH,
(saleprice, saledate)
,GROUP BY
或DISTINCT
将NULL值视为相等。根据您要实现的目的使用适当的查询样式。您仍然可以对DISTINCT ON ()
而不是IS NOT DISTINCT FROM
使用此更快的查询来进行任何或所有比较,以使NULL比较相等。更多:如何删除没有唯一标识符的重复行
如果所有要比较的列都已定义
=
,则没有意见分歧的空间。评论
好答案。我是SQL Server专家,所以我不会想到使用带有IN()检查的元组的第一个建议。不存在的建议通常会在sql服务器中以与内部联接相同的执行计划结束。
–乔尔·科恩(Joel Coehoorn)
2012年9月30日在21:03
真好这种解释大大增加了答案的价值。我几乎想对Oracle进行一些测试,以查看该计划与Postgres和SQLServer的比较。
–彼得
13年8月15日在14:59
@alairock:你从哪儿得到的?对于Postgres,情况恰恰相反。计算所有行时,count(*)比count(
–欧文·布兰德斯特(Erwin Brandstetter)
16-9-29在23:28
@alairock:我碰巧是该页面的合著者,它什么也没说。
–欧文·布兰德斯特(Erwin Brandstetter)
16-10-4在20:36
@ErwinBrandstetter,您总是会在整个堆栈中找到答案。多年来,您以几乎无法想象的方式提供了帮助。对于此示例,我知道几种解决问题的方法,但我希望看到有人测试了各种可能性之间的效率。谢谢。
– WebWanderer
19年5月7日在18:40
#3 楼
查询的问题在于,当使用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,只有在只有一个匹配行的情况下,使用返回列值的函数才是重要的。 br />
#4 楼
我想从“ GrondOfLucht”列中选择不同的值,但是应该按照“排序”列中给出的顺序对它们进行排序。我无法使用Select distinct GrondOfLucht,sortering
from CorWijzeVanAanleg
order by sortering
来获得一列的唯一值,它还会给列“ sortering”,并且因为“ GrondOfLucht”和“ sortering”不是唯一的,结果将是所有行。
使用GROUP来按'sortering
SELECT GrondOfLucht
FROM dbo.CorWijzeVanAanleg
GROUP BY GrondOfLucht, sortering
ORDER BY MIN(sortering)
给定的顺序选择'GrondOfLucht'的记录。
评论
基本上可以解释接受的答案的作用,但我建议您不要使用此类名称作为示例(至少将其翻译)。 PS:我建议始终在所有项目中用英语命名所有内容,即使您荷兰语也是如此。
– Kerwin Sneijders
19年1月14日在20:13
#5 楼
如果您的DBMS不支持像这样的多列分隔符:select distinct(col1, col2) from table
通常可以安全地执行多选,如下所示:
select distinct * from (select col1, col2 from table ) as x
因为它可以在大多数DBMS上运行,并且由于避免了分组功能,所以预计它比按解决方案分组要快。
评论
该查询虽然正确并已被接受一年,但效率极低,因此不必要。不要使用这个。我在另一个答案中提供了替代方法和一些解释。
–欧文·布兰德斯特(Erwin Brandstetter)
2012年9月30日20:45在
SELECT DISTINCT a,b,c FROM t与SELECT a,b,c FROM t GROUP BY a,b,c完全一样吗?
– famargar
17年1月22日在19:47
@famargar对于最简单的情况,但是,它们在语义上具有不同的含义,并且在构建更大的查询时,您可以针对该步骤执行的操作也有所不同。另外,技术论坛上的人通常对事情都非常学究,我发现在这种情况下在我的帖子中添加狡猾的单词通常很有用。
–乔尔·科恩(Joel Coehoorn)
17年1月22日在19:50