如何仅从 MySQL 的字段中选择第一个不同的匹配项?

2021-12-27 00:00:00 sql group-by distinct mysql

如何只返回 MySQL 中某个字段的第一个不同匹配项?

How can I only return the first distinct match of a field in MySQL?

我的表:

name     hash
----------------
Anna     ABC
Barb     DEF
Charlie  GHI
Anna     JKL
Andrea   MNO

我的查询(对于%An%):

SELECT DISTINCT(name) as name, hash FROM my_table WHERE name LIKE '%An%';

返回:

name     hash
----------------
Anna     ABC
Anna     JKL
Andrea   MNO

而不是:(我追求的结果)

name     hash
----------------
Anna     ABC
Andrea   MNO

如何只获取每个不同名称的第一个匹配项?

How can I get only the first match of each distinct name?

我想返回第一个 Anna，跳过第二个(以及任何后续匹配项)，但仍然得到 Andrea(以及任何进一步不同的匹配项，例如 安德鲁 或安东尼).

I want to return the first Anna, skip the second (and any subsequent matches), but still get Andrea (and any further distinct matches, like Andrew or Anthony).

推荐答案

DISTINCT 不能那样工作，返回的所有列的值必须不同.

DISTINCT does not work that way, the values must be distinct across all columns being returned.

您始终可以在 hash 函数和 GROUP BY name 上使用聚合函数，它会为每个 返回一个 hash 值姓名:

You can always use an aggregate function on the hash function and GROUP BY name which will return one hash value for each name:

SELECT name, min(hash) hash
FROM my_table 
WHERE name LIKE '%An%' 
GROUP BY name;

参见SQL Fiddle with Demo.

注意: 将聚合函数与 GROUP BY 一起使用将确保您将始终返回 hash 列的预期值.当您不 GROUP BY 或聚合 SELECT 列表中的项目时，您可能会返回意外结果.(参见 MySQL 对 GROUP BY 的扩展)

Note: using the aggregate function with the GROUP BY will make sure that you will always return the expected value for the hash column. When you do not GROUP BY or aggregate the items in the SELECT list, you might return unexpected results. (see MySQL Extensions to GROUP BY)

来自 MySQL 文档:

From the MySQL Docs:

MySQL 扩展了 GROUP BY 的使用，以便选择列表可以引用未在 GROUP BY 子句中命名的非聚合列....您可以使用此功能通过避免不必要的列排序和分组来获得更好的性能.但是，这主要在未在 GROUP BY 中命名的每个非聚合列中的所有值对于每个组都相同时很有用.服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的.此外，添加 ORDER BY 子句不会影响从每个组中选择值.结果集的排序发生在选择值之后，ORDER BY 不影响服务器选择哪些值.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

相关文章