我需要从一个有两列组合的表格中检索出所有的行。所以我想要所有没有任何其他发生在同一天的相同价格的销售。基于日期和价格的唯一销售将被更新为活动状态。
所以我在想。
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
db<>fiddle here [旧的SQL谜语][2]
如果你没有表的主键或唯一键(例子中的id'),你可以用系统列
ctid'来代替这个查询的目的(但不能用于其他一些目的)。
AND s1.ctid <> s.ctid
每个表都应该有一个主键。如果你还没有,请添加一个。我建议在Postgres 10+中使用serial
或IDENTITY
列。
相关的。
`EXISTS'反半连接中的子查询可以在发现第一个重复后立即停止评估(没有必要继续寻找)。对于一个只有少量重复的基表来说,这只是轻微的效率提高。如果有大量的重复,这将变得**有效。
对于已经有status = 'ACTIVE'
的行,这种更新不会改变任何东西,但仍会以全额费用插入一个新的行版本(小的例外情况适用)。通常情况下,你不希望这样。添加另一个WHERE
条件,就像上面演示的那样,以避免这种情况,并使其更快。
如果status
被定义为NOT NULL
,你可以简化为。
AND status <> 'ACTIVE';
这个查询(与目前被Joel接受的答案不同)并不把NULL值视为相等。以下两行"(saleprice, saledate) "将被视为"不同"(尽管在人眼看来是一样的)。
(123, NULL)
(123, NULL)
在唯一索引和其他任何地方也可以通过,因为根据SQL标准,NULL值不会比较相等。见。
相反,GROUP BY
,DISTINCT
或DISTINCT ON ()
将NULL值视为相等。根据你想达到的目的,使用适当的查询方式。你仍然可以使用这个更快的查询,用IS NOT DISTINCT FROM
代替=
进行任何或所有的比较,使NULL比较相等。更多:
如果所有被比较的列都被定义为NOT NULL
,那么就没有分歧的余地了。
[2]: http://sqlfiddle.com/#! 17/6b5ef/1
你的查询的问题是,当使用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,重要的是在只有一条匹配行的情况下,使用返回该列值的函数。
我想从一列中选择不同的值'GrondOfLucht'。 但它们应该按照列中给出的顺序进行排序'sortering'。 我不能只从一列中得到不同的值,使用
Select distinct GrondOfLucht,sortering
from CorWijzeVanAanleg
order by sortering
它还会给列'分拣'。 并且因为'GrondOfLucht'。 和'sortering'。 不是唯一的,所以结果将是所有行。
使用GROUP来选择'GrondOfLucht'的记录。 的记录,按照'sortering给定的顺序进行选择。
SELECT GrondOfLucht
FROM dbo.CorWijzeVanAanleg
GROUP BY GrondOfLucht, sortering
ORDER BY MIN(sortering)