我需要从表中检索所有行,其中两列的组合都不同。因此,我希望同一天没有任何其他销售的所有销售都以相同的价格进行。基于日期和价格的唯一销售将更新为活动状态。
所以我在想:
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
is roughly相当于:
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
)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)