SQL 如何从选择查询中消除重复项？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4999550/
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 to eliminate duplicates from select query?
提问by ?zbek
Before asking this question I searched using Google but I couldn't understand or maybe could not find a solution suitable for my situation.
在问这个问题之前，我使用 Google 进行了搜索，但我无法理解或者找不到适合我情况的解决方案。
So, I have one Table with 10 columns, I want to eliminate duplicates from selectresult. And in the result all columns should be presented which has unique userID's
所以，我有一个 10 列的表，我想从选择结果中消除重复项。并且在结果中应显示所有具有唯一用户 ID 的列
+-----------------------------------+------+---------------------+------+ | name | yr | some Columns |userID| +-----------------------------------+------+---------------------+------+ | abc | 2000 | | 10 | | Hyman | 2000 | | 11 | | dadas | 2000 | | 12 | | Hyman | 2004 | ............. | 11 | | Hyman | 2000 | ........... | 11 | | nell | 2006 | ............. | 13 | | ...... | 2000 | ............. | 1 | | ............. | 2000 | ............. | 2 | | again | 2000 | ............. | 3 | | again | 2000 | | 3 | | ....... | 1973 | ............. | 2 | | abc | 2000 | | 10 |
回答by criticus
If you don't need to keep different yrs, just use DISTINCT ON (FIELD_NAME)
如果您不需要保持不同的年份，只需使用 DISTINCT ON (FIELD_NAME)
SELECT DISTINCT ON (userID) userdID, name, yr FROM TABLE_NAME
回答by kushalbhaktajoshi
Try this one:
试试这个：
SELECT * FROM TABLE_NAME GROUP BY userID
回答by RichardTheKiwi
For PostgreSQLas well as SQL Server 2005+, DB2 and later versions of Oracle (9+), you can use the windowing function ROW_NUMBER()
对于PostgreSQL以及 SQL Server 2005+、DB2 和更高版本的 Oracle (9+)，您可以使用窗口函数 ROW_NUMBER()
select *
from
(
select *, ROW_NUMBER() over (partition by userID order by yr desc) rown
) X
where rown = 1
回答by jash101
An easy one would be:
一个简单的方法是：
SELECT DISTINCT (userID) userdID, name, yr FROM TABLE_NAME
