如何仅从 MySQL 的字段中选择第一个不同的匹配项？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15010763/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I select only the first distinct match from a field in MySQL?
提问by Ryan
How can I only return the first distinct match of a field in MySQL?
如何只返回 MySQL 中某个字段的第一个不同匹配项？
My Table:
我的表：
name hash
----------------
Anna ABC
Barb DEF
Charlie GHI
Anna JKL
Andrea MNO
My Query (for %An%) :
我的查询（对于%An%）：
SELECT DISTINCT(name) as name, hash FROM my_table WHERE name LIKE '%An%';
This returns:
这将返回：
name hash
----------------
Anna ABC
Anna JKL
Andrea MNO
Instead of: (the result I'm after)
而不是：（我追求的结果）
name hash
----------------
Anna ABC
Andrea MNO
How can I get only the first match of each distinct name?
如何只获得每个不同名称的第一个匹配项？
I want to return the first Anna, skip the second (and any subsequent matches), but still get Andrea(and any further distinct matches, like Andrewor Anthony).
我想返回第一个Anna，跳过第二个（以及任何后续匹配项），但仍然得到Andrea（以及任何进一步不同的匹配项，例如Andrew或Anthony）。
回答by Taryn
DISTINCTdoes not work that way, the values must be distinct across all columns being returned.
DISTINCT不能那样工作，返回的所有列的值必须不同。
You can always use an aggregate function on the hashfunction and GROUP BY namewhich will return one hashvalue for each name:
您始终可以在函数上使用聚合函数，hash并且GROUP BY name将为hash每个函数返回一个值name：
SELECT name, min(hash) hash
FROM my_table
WHERE name LIKE '%An%'
GROUP BY name;
See SQL Fiddle with Demo.
Note:using the aggregate function with the GROUP BYwill make sure that you will always return the expected value for the hashcolumn. When you do not GROUP BYor aggregate the items in the SELECTlist, you might return unexpected results. (see MySQL Extensions to GROUP BY)
注意：将聚合函数与 the 一起使用GROUP BY将确保您将始终返回该hash列的预期值。当您没有GROUP BY或聚合SELECT列表中的项目时，您可能会返回意外的结果。（请参阅MySQL 扩展到GROUP BY）
From the MySQL Docs:
来自 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.
MySQL 扩展了 GROUP BY 的使用，以便选择列表可以引用未在 GROUP BY 子句中命名的非聚合列。...您可以使用此功能通过避免不必要的列排序和分组来获得更好的性能。但是，这主要在未在 GROUP BY 中命名的每个非聚合列中的所有值对于每个组都相同时很有用。服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的。此外，添加 ORDER BY 子句不会影响从每个组中选择值。结果集的排序发生在选择值之后，并且 ORDER BY 不影响服务器选择的值。
回答by Stacker
When using GROUP BY,MySQL destroy the desc order on the same query level.
使用GROUP BY,MySQL 时，在同一查询级别上销毁 desc 顺序。
Instead of:
代替：
SELECT name, hash
FROM my_table
GROUP BY name
ORDER BY name ASC, hash DESC
Use sub query on descending order:
按降序使用子查询：
SELECT * FROM(
SELECT name, hash
FROM my_table
ORDER BY name ASC, hash DESC
)Q
GROUP BY name
回答by echo_Me
try this
尝试这个
SELECT name , hash FROM my_table WHERE name LIKE '%An%'
GROUP BY name;
回答by Slowcoder
SELECT
name, hash
FROM
my_table
WHERE
name LIKE '%An%'
GROUP BY
name;
回答by Binary Alchemist
DISTINCTprovides unique rows of data. In your example hash is different, hence why you are not getting the results you want.
DISTINCT提供唯一的数据行。在您的示例中，哈希是不同的，因此为什么您没有得到想要的结果。
Question:What is hash used for? Do you need it to unique, or is it not needed?
问题：hash 有什么用？你需要它是独一无二的，还是不需要它？
If you do not need it, remove it from the SELECTclause and you will have unique names.
如果您不需要它，请将其从SELECT子句中删除，您将拥有唯一的名称。
If you need it, but it does not need to be unique, you can add a GROUP BYclause, i.e. GROUP BY namewhich will group your results by name (giving you only unique names). Note, using GROUP BYmeans that the value of hash could be either 'ABC' or 'JKL' when name is 'Anna'.
如果你需要它，但它不需要是唯一的，你可以添加一个GROUP BY子句，即GROUP BY name它会按名称对你的结果进行分组（只给你唯一的名称）。注意， usingGROUP BY意味着当 name 是 'Anna' 时，hash 的值可以是 'ABC' 或 'JKL'。
