我需要检索表中的所有行,其中2列组合都是不同的 . 因此,我希望所有在同一天没有任何其他销售的销售以相同的价格销售 . 基于日期和价格的唯一销售将更新为活动状态 .
所以我在想:
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 1)
但是我的大脑比那更远了 .
4 回答
大致相当于:
习惯GROUP BY语法是个好主意,因为它更强大 .
对于您的查询,我会这样做:
如果你把目前为止的答案放在一起,清理并改进,你就会得到这个优越的问题:
这比其中任何一个快得多 . 以10-15的因子(在我对PostgreSQL 8.4和9.1的测试中)来说明当前接受的答案的性能 .
但这还远非最佳 . 使用NOT EXISTS(反)半连接可获得更好的性能 .
EXISTS
是标准的SQL,一直存在(至少自PostgreSQL 7.2以来,很久才提出这个问题)并完全符合所提出的要求:SQL Fiddle.
用于标识行的唯一键
如果您没有表的主键或唯一键(示例中为
id
),则可以使用系统列ctid
替换此查询(但不是出于其他目的):每个表都应该有一个主键 . 如果还没有,请添加一个 . 我建议在Postgres 10中使用序列号或IDENTITY列 .
有关:
In-order sequence generation
Auto increment table column
这怎么更快?
EXISTS
反半连接中的子查询可以在找到第一个欺骗时立即停止评估(没有必要进一步查看) . 对于几乎没有重复的基表,这只是稍微有效 . 有了很多重复,这变得更有效率 .排除空更新
如果某些行或多行已经
status = 'ACTIVE'
,则您的更新不会更改任何内容,但仍会以全部成本插入新行版本(适用次要例外) . 通常,你不希望这样 . 添加另一个WHERE
条件,如上所示,以使其更快:如果
status
定义为NOT NULL
,则可以简化为:NULL处理的细微差别
此查询(与currently accepted answer by Joel不同)不会将NULL值视为相等 .
(saleprice, saledate)
的这两行符合"distinct"(虽然看起来与人眼相同):还传入一个唯一的索引,几乎在任何其他地方,因为根据SQL标准,NULL值不会相等 . 看到:
OTOH,
GROUP BY
或DISTINCT
或DISTINCT ON ()
将NULL值视为相等 . 根据您要实现的目标使用适当的查询样式 . 对于任何或所有比较,您仍然可以使用IS NOT DISTINCT FROM而不是=
来使用此更快的查询样式,以使NULL比较相等 . 更多:如果所有被比较的列都定义为
NOT NULL
,则没有分歧的余地 .您的查询的问题是,当使用GROUP BY子句(您实际上通过使用distinct)时,您只能使用分组的列或聚合函数 . 您不能使用列ID,因为可能存在不同的值 . 在你的情况下,由于HAVING子句,总是只有一个值,但大多数RDBMS都不够聪明,无法识别 .
这应该工作(并且不需要连接):
您也可以使用MAX或AVG而不是MIN,如果只有一个匹配的行,使用一个返回列值的函数是很重要的 .
我想从一列'GrondOfLucht'中选择不同的值,但它们应按照'sortering'列中给出的顺序排序 . 我无法使用只有一列的明确值
它还会给列“分拣”,因为'GrondOfLucht'和'分拣'不是唯一的,结果将是所有行 .
使用GROUP按照'sortering'给出的顺序选择'GrondOfLucht'的记录