关于sql:如何(或可以)在多列上选择DISTINCT?

关于sql:如何(或可以)在多列上选择DISTINCT?

How do I (or can I) SELECT DISTINCT on multiple columns?

我需要从合并了2列的表中检索所有行。 因此,我希望所有在同一天以相同价格进行的销售都没有其他销售。 基于日期和价格的唯一销售将更新为活动状态。

所以我在想:

1
2
3
4
5
UPDATE sales
SET STATUS = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, COUNT(id)
             FROM sales
             HAVING COUNT = 1)

但是我的大脑比这更痛。


1
SELECT DISTINCT a,b,c FROM t

大致相当于:

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

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

对于您的查询,我会这样做:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
 )

如果将到目前为止的答案汇总在一起,进行清理和改进,您将得出以下高级查询:

1
2
3
4
5
6
7
8
UPDATE sales
SET    STATUS = 'ACTIVE'
WHERE  (saleprice, saledate) IN (
    SELECT saleprice, saledate
    FROM   sales
    GROUP  BY saleprice, saledate
    HAVING COUNT(*) = 1
    );

这比任何一个都快得多。核对当前接受的答案的性能(系数为10-15)(在PostgreSQL 8.4和9.1上的测试中)。

但这仍然远非最佳。使用NOT EXISTS(反)半联接可获得更好的性能。 EXISTS是标准SQL,已经存在了很长时间(至少从PostgreSQL 7.2开始,很久以前才问这个问题),并且完全符合提出的要求:

1
2
3
4
5
6
7
8
9
UPDATE sales s
SET    STATUS = 'ACTIVE'
WHERE  NOT EXISTS (
   SELECT FROM sales s1                     -- SELECT list can be empty for EXISTS
   WHERE  s.saleprice = s1.saleprice
   AND    s.saledate  = s1.saledate
   AND    s.id <> s1.id                     -- except for row itself
   )
AND    s.status IS DISTINCT FROM 'ACTIVE';  -- avoid empty updates. see below

db <>在这里拨弄
旧的SQL提琴

识别行的唯一键

如果您没有该表的主键或唯一键(在示例中为id),则可以使用系统列ctid代替该查询(但不能用于其他目的):

1
   AND    s1.ctid <> s.ctid

每个表都应有一个主键。如果还没有,请添加一个。我建议在Postgres 10+中使用serialIDENTITY列。

有关:

  • 有序序列生成
  • 自动增量表列

怎么更快?

一旦找到第一个重复对象,EXISTS反半联接中的子查询就可以停止评估(进一步寻找毫无意义)。对于几乎没有重复的基本表,这只会稍微提高效率。如果重复很多,这将变得更有效率。

排除空白更新

对于已经具有status = 'ACTIVE'的行,此更新不会更改任何内容,但仍会全额插入新的行版本(有少量例外)。通常,您不希望这样做。如上所示,添加另一个WHERE条件来避免这种情况并使它变得更快:

如果将status定义为NOT NULL,则可以简化为:

1
AND STATUS <> 'ACTIVE';

NULL处理的细微差别

此查询(与Joel当前接受的答案不同)不会将NULL值视为相等。 (saleprice, saledate)的以下两行将被视为"与众不同"(尽管看上去与人眼相同):

1
2
(123, NULL)
(123, NULL)

还传递唯一索引和几乎其他任何地方,因为NULL值根据SQL标准不等于相等。看到:

  • 使用空列创建唯一约束

OTOH,GROUP BYDISTINCTDISTINCT ON ()将NULL值视为相等。根据您要实现的目的使用适当的查询样式。您仍然可以对IS NOT DISTINCT FROM而不是=使用此更快的查询进行任何或所有比较,以使NULL比较相等。更多:

  • 如何删除没有唯一标识符的重复行

如果所有要比较的列均定义为NOT NULL,则没有分歧的余地。


查询的问题在于,当使用GROUP BY子句时(本质上是通过使用distinct来完成的),您只能使用按功能分组或聚合功能的列。您不能使用列ID,因为可能存在不同的值。在您的情况下,由于HAVING子句,始终只有一个值,但是大多数RDBMS不够聪明,无法识别该值。

但是,这应该可以工作(并且不需要联接):

1
2
3
4
5
6
7
UPDATE sales
SET STATUS='ACTIVE'
WHERE id IN (
  SELECT MIN(id) FROM sales
  GROUP BY saleprice, saledate
  HAVING COUNT(id) = 1
)

您也可以使用MAX或AVG代替MIN,只有只有一个匹配的行时,使用返回列值的函数才是重要的。


我想从" GrondOfLucht"列中选择不同的值,但是应该按照"排序"列中给出的顺序对它们进行排序。我无法使用仅获得一列的不同值

1
2
3
SELECT DISTINCT GrondOfLucht,sortering
FROM CorWijzeVanAanleg
ORDER BY sortering

它还将给列"排序",并且因为" GrondOfLucht"和"排序"不是唯一的,所以结果将是所有行。

使用"组"以"排序"给定的顺序选择" GrondOfLucht"的记录

1
2
3
4
SELECT        GrondOfLucht
FROM            dbo.CorWijzeVanAanleg
GROUP BY GrondOfLucht, sortering
ORDER BY MIN(sortering)

如果您的DBMS不支持像这样的多个列,则:

1
SELECT DISTINCT(col1, col2) FROM TABLE

通常,可以安全执行多重选择,如下所示:

1
SELECT DISTINCT * FROM (SELECT col1, col2 FROM TABLE ) AS x

因为这可以在大多数DBMS上使用,并且由于避免了分组功能,所以预计它比按解决方案分组要快。


推荐阅读

    学习写字楼新选择6000元主流配置

    学习写字楼新选择6000元主流配置,,这种配置需要考虑双核心的办公和娱乐平台,充分考虑办公室的办公需求和娱乐需求,以约6000元的预算和cost-e

    wps如何合并章节

    wps如何合并章节,WPS教程,1.WPS表格如何实现单元格合并?WPS 表格在新版本中增加了“合并单元格”系列按钮,同时配有下拉菜单和快捷键。新增

    玩游戏,i7/i5如何选择

    玩游戏,i7/i5如何选择,,CPU和显卡都在不断更新,每年都有越来越多的性能和特点,但它不一定对每个球员的必要。作为最强的英特尔旗舰处理器酷睿

    自己配置电脑选择cpu|电脑配置怎样选

    自己配置电脑选择cpu|电脑配置怎样选,,电脑配置怎样选买笔记本电脑主要看CPU、显卡、主板、内存、硬盘等硬件的性能参数,当然最关键的是考

    499元SNB性价比ASLp61t板新的选择建议

    499元SNB性价比ASLp61t板新的选择建议,,H61的产品由于价格优势,也可以适应和Turbo SNB发布高清解码和游戏性能已成为最后的主流价格非常优

    fcpx快捷键大全|fcpx选择快捷键

    fcpx快捷键大全|fcpx选择快捷键,,fcpx选择快捷键快捷键是T或者按俩下T 。fcpx所有快捷键先单独试下每个这两个键位,不开任何程序的情况下