Mysql 选择不同
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7250566/
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
Mysql select distinct
提问by Shanon
I am trying to select of the duplicate rows in mysql table it's working fine for me but the problem is that it is not letting me select all the fields in that query , just letting me select the field name i used as distinct , lemme write the query for better understading
我正在尝试选择 mysql 表中的重复行，它对我来说工作正常，但问题是它不允许我选择该查询中的所有字段，只是让我选择我用作不同的字段名称，让我写下查询更好的理解
mysql_query("SELECT DISTINCT ticket_id FROM temp_tickets ORDER BY ticket_id")
mysql_query("SELECT * , DISTINCT ticket_id FROM temp_tickets ORDER BY ticket_id")
1st one is working fine
第一个工作正常
now when i am trying to select all fields i am ending up with errors
现在，当我尝试选择所有字段时，我以错误告终
i am trying to select the latest of the duplicates let say ticket_id 127 is 3 times on row id 7,8,9 so i want to select it once with the latest entry that would be 9 in this case and this applies on all the rest of the ticket_id's
我正在尝试选择最新的重复项让说 ticket_id 127 在行 id 7,8,9 上出现 3 次，所以我想选择它一次，最新条目在这种情况下为 9，这适用于所有其他条目ticket_id 的
Any idea thanks
任何想法谢谢
回答by Bill Karwin
DISTINCT is not a function that applies only to some columns. It's a query modifier that applies to all columns in the select-list.
DISTINCT 不是仅适用于某些列的函数。它是一个查询修饰符，适用于选择列表中的所有列。
That is, DISTINCT reduces rows only if allcolumns are identical to the columns of another row.
也就是说，仅当所有列都与另一行的列相同时，DISTINCT 才会减少行。
DISTINCT must follow immediately after SELECT (along with other query modifiers, like SQL_CALC_FOUND_ROWS). Then following the query modifiers, you can list columns.
DISTINCT 必须紧跟在 SELECT 之后（以及其他查询修饰符，如 SQL_CALC_FOUND_ROWS）。然后按照查询修饰符，您可以列出列。
RIGHT:
SELECT DISTINCT foo, ticket_id FROM table...Output a row for each distinct pairing of values across ticket_id andfoo.
WRONG:
SELECT foo, DISTINCT ticket_id FROM table...If there are three distinct values of ticket_id, would this return only three rows? What if there are six distinct values of foo? Which three values of the six possible values of foo should be output?
It's ambiguous as written.
对：
SELECT DISTINCT foo, ticket_id FROM table...为 ticket_id和foo 中每个不同的值对输出一行。
错误的：
SELECT foo, DISTINCT ticket_id FROM table...如果ticket_id 有三个不同的值，这是否只返回三行？如果 foo 有六个不同的值怎么办？应该输出 foo 的六个可能值中的哪三个值？
写的很含糊。
回答by a1ex07
Are you looking for "SELECT * FROM temp_tickets GROUP BY ticket_id ORDER BY ticket_id?
你在找"SELECT * FROM temp_tickets GROUP BY ticket_id ORDER BY ticket_id吗？
UPDATE
更新
SELECT t.*
FROM
(SELECT ticket_id, MAX(id) as id FROM temp_tickets GROUP BY ticket_id) a
INNER JOIN temp_tickets t ON (t.id = a.id)
回答by John
You can use group byinstead of distinct. Because when you use distinct, you'll get struggle to select all values from table. Unlike when you use group by, you can get distinct values and also all fields in table.
您可以使用group by而不是 distinct。因为当您使用 distinct 时，您将很难从表中选择所有值。与使用 group by 不同，您可以获得不同的值以及表中的所有字段。
回答by Rahul Sharma
You can use like that
你可以这样使用
mysql_query("SELECT DISTINCT(ticket_id), column1, column2, column3 FROM temp_tickets ORDER BY ticket_id")
mysql_query("SELECT DISTINCT(ticket_id), column1, column2, column3 FROM temp_tickets ORDER BY ticket_id")
