SQL查询去重但“保留第一个”？

Question

SQL查询去重但“保留第一个”？

9

根据另一个 SO 帖子（SQL: How to keep rows order with DISTINCT?），distinct 在排序方面具有相当不确定的行为。

我有一个查询：

select col_1 from table order by col_2

这可以返回像

的值

我需要在这些内容中选择一个保留排序的不同项，也就是说我想要

select distinct(col_1) from table order by col_2

返回

3
5
2

但不是

5
3
2

这里是我实际想要做的事情。Col_1是用户ID，col_2是该用户的登录时间戳事件。因此，同一用户（col_1）可以有多个登录时间。我试图构建一个历史用户列表，其中显示他们在系统中出现的时间。我希望能够说“我们的第一个用户是，我们的第二个用户是”，以此类推。

那篇文章似乎建议使用group by，但group by并不意味着返回行的排序，因此我不明白为什么会适用于这里，因为它似乎没有保留任何排序。实际上，另一篇SO文章给出了一个例子，其中group by将破坏我正在寻找的排序：请参见sql中GROUP BY和ORDER BY之间的区别中的“Peter”。有没有办法保证后者的结果？奇怪的是，如果是我实现DISTINCT子句，我肯定会先按顺序进行排序，然后取结果并对列表进行线性扫描，并自然地保留排序，所以我不确定为什么行为如此未定义。

编辑：

谢谢大家！我接受了IMSoP的答案，因为他不仅提供了一个互动示例供我玩耍（感谢你让我知道SQL Fiddle），而且还解释了为什么一些东西能够工作，而不仅仅是“这样做”。具体来说，GROUP BY并不会破坏（而是将它们保留在某种内部列表中）组外其他列中的值，这些值仍然可以在ORDER BY子句中进行检查。

- Tommy

3

如果您没有提供 ORDER BY 子句，数据的顺序将不能得到保证。如果您想按照特定的顺序排列数据，那么必须使用 order by。 - Taryn

1

我正在使用mysql 5.6，但我认为DISTINCT可能在SQL标准的某个地方被定义了。 - Tommy

1

没有未定义的行为。它几乎被定义了：不带 ORDER BY 的查询可以按任意顺序返回其结果集。如果您想要特定的顺序，请使用 ORDER BY。如果没有 ORDER BY，则结果就是一个表。对于表来说，顺序并不重要。 - ypercubeᵀᴹ

@Tommy：我不太同意。问题是关于选择第一个球，而不是选择被插入的第一个球。（但我认为我们基本上是同意的……） - wildplasser

1

@Tommy：除非在记录中写下了第一个的信息（例如通过时间戳或序列号），否则就没有所谓的“第一个”。如果没有这些信息，所有红球都是红色的，没有一个可以被选为“第一个”。 - wildplasser

显示剩余18条评论

4个回答

2

我会选择类似这样的东西。

select col1
from (
select col1,
       rank () over(order by col2) pos
from table
)
group by col1
order by min(pos)

在子查询中，我计算位置，然后在主查询中对col1进行分组，使用最小位置进行排序。

这里是SQLFiddle中的演示（此为Oracle版本，MySql信息稍后添加）。

针对MySql的编辑：

select col1
from (
select col1 col1,
       @curRank := @curRank + 1 AS pos
from table1, (select @curRank := 0) p
) sub
group by col1
order by min(pos)

这里是MySql的演示。

- mucio

这个查询不需要变量。 - ypercubeᵀᴹ

这对于 OP 需要的仅仅是更好地理解 GROUP BY 和聚合来说过于浪费了。如果您按未分组的列进行排序，通常希望使用该列的 min() 或 max()。 - IMSoP

我知道，但解决方案是如此微不足道，以至于我认为Tommy已经尝试过了 :) 无论如何，如果不能使用min/max，这里有另一种解决方案 :) 顺便问一下，是否有另一种在MySql中实现排名的方法？（如果需要的话） - mucio

1

@mucio 欢迎来到StackOverflow；最好不要对提问者做出任何假设（无论是积极的还是消极的）。如果解决方案似乎太显而易见，请先询问为什么它没有奏效，然后再考虑更高级的解决方案，因为这些可能并不适用于他们的情况。 - IMSoP

1

仅对于MySQL，当您选择不在GROUP BY中的列时，它将返回来自该组中第一条记录的列。您可以使用此行为来选择从每个组返回哪个记录，如下所示：

SELECT foo_number, bar_number
FROM 
(
  SELECT foo_number, bar_number 
  FROM some_table 
  ORDER BY bar_number
) AS t
GROUP BY foo_number
ORDER BY bar_number DESC;

这种方法更加灵活，因为它允许您使用表达式对每个组内的记录进行排序，而聚合函数则无法实现 - 在我的情况下，我想返回另一列中最短字符串的记录。

完整查询如下：

SELECT
  s.NamespaceId,
  s.Symbol,
  s.EntityName
FROM 
(
  SELECT 
    m.NamespaceId,
    i.Symbol, 
    i.EntityName
  FROM ImportedSymbols i
  JOIN ExchangeMappings m ON i.ExchangeMappingId = m.ExchangeMappingId
  WHERE
    i.Symbol NOT IN 
    (
      SELECT Symbol 
      FROM tmp_EntityNames
      WHERE NamespaceId = m.NamespaceId
    )
      AND
    i.EntityName IS NOT NULL
  ORDER BY LENGTH(i.RawSymbol), i.RawSymbol
) AS s
GROUP BY s.NamespaceId, s.Symbol;

这个功能会返回每个名称空间中不同符号的列表，并对重复的符号返回具有最短RawSymbol的符号。当RawSymbol长度相同时，它将返回字母表顺序最先出现的符号。

- bikeman868

1

参考答案中的GROUP BY并不是试图进行排序，它只是为我们想要去重的列选择一个单一的关联值。

就像@bluefeet所说，如果您想要保证有序，您必须使用ORDER BY。

为什么不能在ORDER BY中指定一个在SELECT DISTINCT中未包含的值呢？

考虑以下col1和col2的值：

create table yourTable (
  col_1 int,
  col_2 int
);

insert into yourTable (col_1, col_2) values (1, 1);
insert into yourTable (col_1, col_2) values (1, 3);
insert into yourTable (col_1, col_2) values (2, 2);
insert into yourTable (col_1, col_2) values (2, 4);

使用这些数据，SELECT DISTINCT col_1 FROM yourTable ORDER BY col_2 应该返回什么？

这就是为什么你需要 GROUP BY 和聚合函数来决定你应该按哪个 col_2 的多个值进行排序... 可能是 MIN()，可能是 MAX()，甚至在某些情况下，其他一些函数如 AVG() 也有意义；这完全取决于具体的场景，因此你需要明确指出。

select col_1
from yourTable
group by col_1
order by min(col_2)

`在此处查看SQL Fiddle`

- Michael Fredrickson

如果我们先进行排序，我们得到(1,1) (2,2),(1,3),(2,4)。然后应该返回(1,1)和(2,2)，并且舍弃(1,3)和(2,4)，这正是我想要的结果。那么问题出在哪里呢？ - Tommy

2

你假设distinct只应该返回它遇到的第一个值... 为什么不是最后/最新遇到的值呢？这似乎同样有效，并且可能会给出完全不同的结果。 - Michael Fredrickson

这正是我的观点！至于它是否无效，如果你运行DISTINCT On (3,5,3)，结果(3,5)可以被认为比(5,3)更“自然地暂时排序”。第一个结果对我来说似乎是最合乎逻辑的，因为我已经指定(3,5,3)是按照其他某个条件排序的有序列表。 - Tommy

2

啊，所以说这个参数的意思是distinct应该推断出一个MIN()聚合，因为order by是ASC，而且在时间上MIN()和ASC是相互关联的。但这太主观和具体化了，只适用于特定情况下的意图，不能普遍适用。我同样可以想象一种情况，我想要对我的distinct应用一个MAX()聚合，但是我希望结果按升序排序而不是降序。如果SQL强制要求明确说明期望的行为，而不是试图根据查询的其他部分推断意图，那就简单多了。 - Michael Fredrickson

顺便说一下，这就是为什么我觉得Postgres的DISTINCT ON很难理解：行为是明确定义的，但你必须在SELECT和ORDER BY子句之间跳转才能看到实际返回的数据。 - IMSoP

@IMSoP 这很有趣...我没有Postgres的实际经验，所以今天学到了DISTINCT ON的新知识... - Michael Fredrickson

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- IMSoP · Accepted Answer

这一切与 SQL 语句的“逻辑顺序”有关。尽管 DBMS 可以根据各种聪明的策略实际检索数据，但它必须按照某些可预测的逻辑行为。因此，SQL 查询的不同部分可以被认为是根据逻辑如何行为而“先于”或“晚于”彼此处理的。

恰好，ORDER BY 子句是该逻辑顺序中的最后一步，因此它不能改变“早期”步骤的行为。

如果使用 GROUP BY，在运行 SELECT 子句，更不用说 ORDER BY 子句之前，行已经被分组打包了，因此您只能查看已被分组或跨组计算的“聚合”列值。（MySQL 实现了 GROUP BY 的一个有争议的扩展，其中您可以在 SELECT 中提到一个逻辑上不存在的列，并且它将从该组中的任意一行中选择一行）。

如果使用 DISTINCT，它在逻辑上是在 SELECT 之后处理的，但是 ORDER BY 仍然在其后面。因此，只有在 DISTINCT 扔掉重复项后，剩下的结果才会被放入特定的顺序中——但已被丢弃的行不能用于确定该顺序。

至于如何获取您需要的结果，关键在于找到一个在经过 GROUP BY/DISTINCT（逻辑上）运行后仍然有效的排序值。请记住，如果使用 GROUP BY，任何聚合值仍然有效——聚合函数可以查看组中的所有值。这包括 MIN() 和 MAX()，它们非常适合排序，因为“最低数字”(MIN)与“如果我按升序排序，则第一个数字”相同，反之亦然。

因此，要根据每个唯一的 foo_number 值的最低适用的 bar_number 进行排序，可以使用以下内容：

SELECT foo_number
FROM some_table
GROUP BY foo_number
ORDER BY MIN(bar_number) ASC

这里有一个带有一些任意数据的实时演示。

编辑： 在评论中，讨论了在分组/去重之前应用排序时，为什么不将该顺序应用于组。如果是这种情况，您仍需要一个策略来确定每个组保留哪一行：第一行还是最后一行。

类比一下，将原始行集想象成从一副牌中挑选出来的一组扑克牌，然后按其面值从低到高排序。现在按照花色将已排序的牌分发到各自的一叠牌中。哪张牌应该代表每叠牌?

如果正面朝上发牌，则显示在最后的牌是具有更高面值的牌（“保留最后”策略）; 如果你把它们背面向下地发牌，然后翻开每一叠牌，你会发现面值最低的牌（“保留第一”策略）。两者都遵循牌的原始顺序，并且“根据花色发牌”的指示并不能自动告诉代表DBMS的荷官应该采用哪种策略。

如果最终的牌叠是GROUP BY,那么MIN()和MAX()表示拿起每叠牌并寻找最低或最高值，而不管它们的顺序如何。但是，因为您可以查看组内部，所以您也可以做其他事情，例如将每叠牌的总价值相加(SUM)或有多少张牌(COUNT)等，使GROUP BY比“排序后的DISTINCT”更强大。