SQL 如何只为列的每个唯一值选择第一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4662464/
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 select only the first rows for each unique value of a column
提问by nuit9
Let's say I have a table of customer addresses:
假设我有一张客户地址表：
CName | AddressLine
-------------------------------
John Smith | 123 Nowheresville
Jane Doe | 456 Evergreen Terrace
John Smith | 999 Somewhereelse
Joe Bloggs | 1 Second Ave
In the table, one customer like John Smith can have multiple addresses. I need the select query for this table to return only first row found where there are duplicates in 'CName'. For this table it should return all rows except the 3rd (or 1st - any of those two addresses are okay but only one can be returned). Is there a keyword I can add to the SELECT query to filter based on whether the server has already seen the column value before?
在表中，像 John Smith 这样的一位客户可以有多个地址。我需要此表的选择查询仅返回在“CName”中有重复项的第一行。对于这个表，它应该返回除第三行（或第一行 - 这两个地址中的任何一个都可以，但只能返回一个）之外的所有行。是否有一个关键字可以添加到 SELECT 查询中以根据服务器之前是否已经看到列值进行过滤？
回答by gbn
A very simple answer if you say you don't care which address is used.
如果您说您不在乎使用哪个地址，那么这是一个非常简单的答案。
SELECT
CName, MIN(AddressLine)
FROM
MyTable
GROUP BY
CName
If you want the first according to, say, an "inserted" column then it's a different query
如果你想要第一个根据，比如说，一个“插入”的列，那么它是一个不同的查询
SELECT
M.CName, M.AddressLine,
FROM
(
SELECT
CName, MIN(Inserted) AS First
FROM
MyTable
GROUP BY
CName
) foo
JOIN
MyTable M ON foo.CName = M.CName AND foo.First = M.Inserted
回答by Ben Thul
In SQL 2k5+, you can do something like:
在 SQL 2k5+ 中，您可以执行以下操作：
;with cte as (
select CName, AddressLine,
rank() over (partition by CName order by AddressLine) as [r]
from MyTable
)
select CName, AddressLine
from cte
where [r] = 1
回答by Frank
You can use row_number()to get the row number of the row. It uses the overcommand - the partition byclause specifies when to restart the numbering and the order byselects what to order the row number on. Even if you added an order byto the end of your query, it would preserve the ordering in the overcommand when numbering.
您可以使用row_number()来获取行的行号。它使用over命令 - 该partition by子句指定何时重新开始编号并order by选择对行号进行排序的内容。即使您order by在查询的末尾添加了，它也会over在编号时保留命令中的顺序。
select *
from mytable
where row_number() over(partition by Name order by AddressLine) = 1
回答by FatihAkici
You can use the row_numer() over(partition by ...)syntax like so:
您可以row_numer() over(partition by ...)像这样使用语法：
select * from
(
select *
, ROW_NUMBER() OVER(PARTITION BY CName ORDER BY AddressLine) AS row
from myTable
) as a
where row = 1
What this does is that it creates a column called row, which is a counter that increments every time it sees the same CName, and indexes those occurrences by AddressLine. By imposing where row = 1, one can select the CNamewhose AddressLinecomes first alphabetically. If the order bywas desc, then it would pick the CNamewhose AddressLinecomes last alphabetically.
它的作用是创建一个名为 的列row，该列是一个计数器，每次看到相同的值时都会递增CName，并按 对这些出现进行索引AddressLine。通过征收where row = 1，可以选择CName其AddressLine来首字母顺序排列。如果order by是desc，那么它将选择按字母顺序排在最后的CName那个AddressLine。
回答by netfed
This will give you one row of each duplicate row. It will also give you the bit-type columns, and it works at least in MS Sql Server.
这将为您提供每个重复行的一行。它还将为您提供位类型列，并且它至少可以在 MS Sql Server 中工作。
(select cname, address
from (
select cname,address, rn=row_number() over (partition by cname order by cname)
from customeraddresses
) x
where rn = 1) order by cname
If you want to find all the duplicates instead, just change the rn= 1 to rn > 1. Hope this helps
如果您想查找所有重复项，只需将 rn= 1 更改为 rn > 1。希望这会有所帮助
