我需要从一个表中检索所有行,其中 2 列组合起来都不同。所以我想要所有在同一天以相同价格发生的没有任何其他销售的销售。基于日期和价格的唯一销售将更新为活动状态。
所以我在想:
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 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
)
如果您将到目前为止的答案放在一起,进行清理和改进,您将得到这个高级查询:
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
定义为 NOT NULL
,您可以简化为:
AND status <> 'ACTIVE';
列的数据类型必须支持 <>
运算符。像 json
这样的某些类型不这样做。看:
如何查询空对象的 json 列?
NULL 处理的细微差别
此查询(与 currently accepted answer by Joel 不同)不将 NULL 值视为相等。 (saleprice, saledate)
的以下两行将被视为“不同”(虽然看起来与人眼相同):
(123, NULL)
(123, NULL)
还传入一个唯一索引和几乎其他任何地方,因为根据 SQL 标准,NULL 值不比较相等。看:
使用空列创建唯一约束
OTOH、GROUP BY
、DISTINCT
或 DISTINCT ON ()
将 NULL 值视为相等。根据您想要实现的目标使用适当的查询样式。您仍然可以将这个更快的查询与 IS NOT DISTINCT FROM
而不是 =
一起用于任何或所有比较,以使 NULL 比较相等。更多的:
如何删除没有唯一标识符的重复行
如果所有要比较的列都定义为 NOT NULL
,则没有分歧的余地。
count(*)
比 count(<expression>)
的效率更高。去尝试一下。 Postgres 对聚合函数的这种变体有更快的实现。也许您将 Postgres 与其他一些 RDBMS 混淆了?
您的查询的问题在于,当使用 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,如果只有一个匹配行,则使用返回列值的函数很重要。
如果您的 DBMS 不支持具有多个列的 distinct,如下所示:
select distinct(col1, col2) from table
多选通常可以安全地执行如下:
select distinct * from (select col1, col2 from table ) as x
因为这可以在大多数 DBMS 上工作,并且由于您避免了分组功能,因此预计这比按解决方案分组更快。
SELECT DISTINCT ON (col_a) id,col_a FROM Table
;
我想从一列“GrondOfLucht”中选择不同的值,但它们应该按照“排序”列中给出的顺序进行排序。我无法使用仅获得一列的不同值
Select distinct GrondOfLucht,sortering
from CorWijzeVanAanleg
order by sortering
它还将给出“sortering”列,因为“GrondOfLucht”和“sortering”不是唯一的,结果将是所有行。
使用 GROUP 按“sortering”给出的顺序选择“GrondOfLucht”的记录
SELECT GrondOfLucht
FROM dbo.CorWijzeVanAanleg
GROUP BY GrondOfLucht, sortering
ORDER BY MIN(sortering)