我需要从合并了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 )