我如何（或可以）在多列上选择DISTINCT？

本文介绍了我如何（或可以）在多列上选择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

大致相当于：

is roughly equivalent to:

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 )