我需要从表中检索所有行,其中 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 )