SQL：在 where 子句中不同
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14729409/
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
SQL: distinct in where clause
提问by ADM
I have a query that after execution shows:
我有一个查询，执行后显示：
Name | LasName | City
-------------------------
John | Doe | London
John | Doe | Berlin
Martin| Smith | Paris
Is there anything I can do in the WHERE clause section in order to get just one John Doe, no matter the city?
无论在哪个城市，我都可以在 WHERE 子句部分做些什么来只获得一个 John Doe？
The query is humongous that's why I'm not posting it in here.
查询是巨大的，这就是为什么我不在这里发布它。
If there's anything you think I can do, please, post a fake query explaining it, I just need to get the concept.
如果您认为我可以做任何事情，请发布一个虚假的查询来解释它，我只需要了解这个概念。
Thanks a lot!
非常感谢！
回答by Martin Smith
You don't state RDBMS. One way that should work in all.
您没有说明 RDBMS。一种应该适用于所有人的方法。
SELECT Name,
LasName,
MAX(City) AS City
FROM YourQuery
GROUP BY Name,
LasName
You say you don't care which city. This gives you the last alphabetically.
你说你不在乎哪个城市。这给你最后一个字母。
Or an alternative
或者替代方案
SELECT Name,
LasName,
City
FROM (SELECT Name,
LasName,
City,
ROW_NUMBER() OVER (PARTITION BY Name, LasName ORDER BY (SELECT 0)) AS RN
FROM YourQuery) T
WHERE RN = 1
回答by Martin Smith
Assuming you're using MySQL and you want to see all the cities for the same user name on one line, try something like:
假设您正在使用 MySQL 并且您希望在一行中查看相同用户名的所有城市，请尝试以下操作：
select Name, LasName, group_concat(City)
from LargeQuery
group by Name, LasName
回答by Manolis
You can use the following query (t-sql)
您可以使用以下查询（t-sql）
SELECT Distinct Name,LasName
FROM TableName
回答by Tapas Pal
select * from `TableName` group by Name, LasName
回答by Orangecrush
Assuming that you are asking to query just one row, irrespective of the city, try this,
假设您只要求查询一行，而不考虑城市，试试这个，
select * from tablename
where name = 'John'
and lasname = 'Doe'
and rownum = 1;
