sql - 如何（或可以）在多列上选择DISTINCT？(How do I (or can I) SELECT DISTINCT on multiple columns?)

Question

I need to retrieve all rows from a table where 2 columns combined are all different.

(我需要从合并了2列的表中检索所有行。)

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.

(基于日期和价格的唯一销售将更新为活动状态。)

So I'm thinking:

(所以我在想：)

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.

(但是我的大脑比这更痛。)

  ask by sheats translate from so

Answer 1

SELECT DISTINCT a,b,c FROM t

is roughly equivalent to:

(大致相当于：)

SELECT a,b,c FROM t GROUP BY a,b,c

It's a good idea to get used to the GROUP BY syntax, as it's more powerful.

(习惯GROUP BY语法是一个好主意，因为它更强大。)

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
 )