我如何(或我可以)在多列上选择 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

大致相当于:

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屋！