我怎样才能返回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

我怎样才能获得每个不同名称的第一个匹配？

我想要返回第一个安娜,跳过第二个(以及任何后续比赛),但仍然得到安德里亚(以及任何进一步的不同比赛,如安德鲁或安东尼).

解决方法:

DISTINCT不能以这种方式工作,值必须在返回的所有列之间是不同的.

您始终可以在散列函数和GROUP BY名称上使用聚合函数,该函数将为每个名称返回一个散列值：

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

见SQL Fiddle with Demo.

注意：将聚合函数与GROUP BY一起使用将确保始终返回哈希列的预期值.如果您没有GROUP BY或聚合SELECT列表中的项目,您可能会返回意外的结果. (见MySQL Extensions to GROUP BY)

来自MySQL Docs：

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.