如何消除选择查询中的重复？

Before asking this question I searched using Google but I couldn't understand or maybe could not find a solution suitable for my situation.

在提出这个问题之前,我使用谷歌进行了搜索,但我无法理解或者找不到适合我情况的解决方案。

So, I have one Table with 10 columns, I want to eliminate duplicates from select result. And in the result all columns should be presented which has unique userID's

所以,我有一个包含10列的表,我想从选择结果中消除重复。并且在结果中应该呈现具有唯一用户ID的所有列

+-----------------------------------+------+---------------------+------+
| name                              | yr   |   some Columns      |userID|
+-----------------------------------+------+---------------------+------+
| abc                               | 2000 |                     |   10 |
| jack                              | 2000 |                     |   11 |
| dadas                             | 2000 |                     |   12 |
| jack                              | 2004 | .............       |   11 |
| jack                              | 2000 | ...........         |   11 |
| nell                              | 2006 | .............       |   13 |
| ......                            | 2000 | .............       |   1  |
| .............                     | 2000 | .............       |   2  |
| again                             | 2000 | .............       |   3  |
| again                             | 2000 |                     |   3  |
| .......                           | 1973 | .............       |   2  |
| abc                               | 2000 |                     |   10 |

4 个解决方案

#1

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

#2

Try this one:

试试这个:

SELECT * FROM TABLE_NAME GROUP BY userID

#3

For PostgreSQL as 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

#4

An easy one would be:

一个简单的方法是:

SELECT DISTINCT (userID) userdID, name, yr FROM TABLE_NAME

#1