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

问题描述

我需要检索一个表中的所有行，其中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
 )

这篇关于我如何（或可以）在多列上选择DISTINCT？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！