mysql - 如何只从 MySQL 的字段中选择第一个不同的匹配项？

Question

如何只返回 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

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

我想返回第一个 Anna，跳过第二个(和任何后续匹配项)，但仍然得到 Andrea(以及任何其他不同的匹配项，例如 Andrew 或 Anthony)。

Answer 1

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

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

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 Extensions to GROUP BY)

来自 MySQL 文档:

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.