SQL - 仅在一列上选择不同
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20406069/
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 - select distinct only on one column
提问by Jason Lipo
I have searched far and wide for an answer to this problem. I'm using a Microsoft SQL Server, suppose I have a table that looks like this:
我已经广泛搜索了这个问题的答案。我正在使用 Microsoft SQL Server，假设我有一个如下所示的表：
+--------+---------+-------------+-------------+
| ID | NUMBER | COUNTRY | LANG |
+--------+---------+-------------+-------------+
| 1 | 3968 | UK | English |
| 2 | 3968 | Spain | Spanish |
| 3 | 3968 | USA | English |
| 4 | 1234 | Greece | Greek |
| 5 | 1234 | Italy | Italian |
I want to perform one query which only selects the unique 'NUMBER' column (whether is be the first or last row doesn't bother me). So this would give me:
我想执行一个查询，它只选择唯一的“NUMBER”列（第一行还是最后一行都不打扰我）。所以这会给我：
+--------+---------+-------------+-------------+
| ID | NUMBER | COUNTRY | LANG |
+--------+---------+-------------+-------------+
| 1 | 3968 | UK | English |
| 4 | 1234 | Greece | Greek |
How is this achievable?
这是如何实现的？
采纳答案by Kyle Hale
Since you don't care, I chose the max ID for each number.
既然你不在乎，我为每个号码选择了最大 ID。
select tbl.* from tbl
inner join (
select max(id) as maxID, number from tbl group by number) maxID
on maxID.maxID = tbl.id
Query Explanation
查询说明
select
tbl.* -- give me all the data from the base table (tbl)
from
tbl
inner join ( -- only return rows in tbl which match this subquery
select
max(id) as maxID -- MAX (ie distinct) ID per GROUP BY below
from
tbl
group by
NUMBER -- how to group rows for the MAX aggregation
) maxID
on maxID.maxID = tbl.id -- join condition ie only return rows in tbl
-- whose ID is also a MAX ID for a given NUMBER
回答by Gordon Linoff
A very typical approach to this type of problem is to use row_number():
解决此类问题的一种非常典型的方法是使用row_number()：
select t.*
from (select t.*,
row_number() over (partition by number order by id) as seqnum
from t
) t
where seqnum = 1;
This is more generalizable than using a comparison to the minimum id. For instance, you can get a random row by using order by newid(). You can select 2 rows by using where seqnum <= 2.
这比使用与最小 id 的比较更具有普遍性。例如，您可以使用order by newid(). 您可以使用 选择 2 行where seqnum <= 2。
回答by Gedalya
You will use the following query:
您将使用以下查询：
SELECT * FROM [table] GROUP BY NUMBER;
Where [table]is the name of the table.
[table]表名在哪里。
This provides a unique listing for the NUMBERcolumn however the other columns may be meaningless depending on the vendor implementation; which is to say they may not together correspond to a specific row or rows.
这为NUMBER列提供了一个唯一的列表，但是其他列可能没有意义，具体取决于供应商的实现；也就是说，它们可能不会一起对应于特定的一行或多行。
