本文介绍了我如何(或可以)在多列上选择 DISTINCT?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要从一个表中检索所有行,其中 2 列组合都不同.所以我想要所有没有在同一天以相同价格发生的任何其他销售的销售.基于日期和价格的独特销售将更新为有效状态.
I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales that are unique based on day and price will get updated to an active status.
所以我在想:
UPDATE sales SET status = 'ACTIVE' WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id) FROM sales HAVING count = 1)但是我的脑子比这更痛.
But my brain hurts going any farther than that.
推荐答案 SELECT DISTINCT a,b,c FROM t大致相当于:
SELECT a,b,c FROM t GROUP BY a,b,c习惯 GROUP BY 语法是个好主意,因为它更强大.
It's a good idea to get used to the GROUP BY syntax, as it's more powerful.
对于您的查询,我会这样做:
For your query, I'd do it like this:
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 )更多推荐
我如何(或可以)在多列上选择 DISTINCT?
发布评论