SQL 查询为列中的每个唯一值返回一条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/982932/
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 query to return one single record for each unique value in a column
提问by recursive
I have a table in SQL Server 2000 that I am trying to query in a specific way. The best way to show this is with example data.
我在 SQL Server 2000 中有一个表，我试图以特定方式查询它。展示这一点的最佳方式是使用示例数据。
Behold, [Addresses]:
看，[Addresses]：
Name Street City State
--------------------------------------------------------
Bob 123 Fake Street Peoria IL
Bob 234 Other Street Fargo ND
Jim 345 Main Street St Louis MO
This is actually a simplified example of the structure of the actual table. The structure of the table is completely beyond my control. I need a query that will return a single address per name. It doesn't matter which address, just that there is only one. The result could be this:
这实际上是实际表结构的简化示例。表的结构完全超出我的控制。我需要一个查询，该查询将为每个名称返回一个地址。哪个地址并不重要，只是只有一个。结果可能是这样的：
Name Street City State
--------------------------------------------------------
Bob 123 Fake Street Peoria IL
Jim 345 Main Street St Louis MO
I found a similar question here, but none of the solutions given work in my case because I do not have access to CROSS APPLY, and calling MIN()on each column will mix different addresses together, and although I don't care which record is returned, it must be one intact row, not a mix of different rows.
我在这里发现了一个类似的问题，但是在我的情况下给出的解决方案都没有工作，因为我无权访问CROSS APPLY，并且调用MIN()每一列会将不同的地址混合在一起，尽管我不在乎返回哪条记录，但它必须是一个完整的行，而不是不同行的混合。
Recommendations to change the table structure will not help me. I agree that this table is terrible, (it's worse than shown here) but this is part of a major ERP database that I can not change.
更改表结构的建议对我没有帮助。我同意这张表很糟糕，（它比这里显示的更糟糕）但这是我无法更改的主要 ERP 数据库的一部分。
There are about 3000 records in this table. There is no primary key.
该表中大约有 3000 条记录。没有主键。
Any ideas?
有任何想法吗？
采纳答案by Brimstedt
Well, this will give you pretty bad performance, but I think it'll work
好吧，这会给你带来非常糟糕的表现，但我认为它会起作用
SELECT t.Name, t.Street, t.City, t.State
FROM table t
INNER JOIN (
SELECT m.Name, MIN(m.Street + ';' + m.City + ';' + m.State) AS comb
FROM table m
GROUP BY m.Name
) x
ON x.Name = t.Name
AND x.comb = t.Street + ';' + t.City + ';' + t.State
回答by Gratzy
Use a temp table or table variable and select a distinct list of names into that. Use that structure then to select the top 1 of each record in the original table for each distinct name.
使用临时表或表变量并在其中选择一个不同的名称列表。然后使用该结构为每个不同的名称选择原始表中每个记录的前 1 个。
回答by Shannon Severance
If you can use a temp table:
如果您可以使用临时表：
select * -- Create and populate temp table
into #Addresses
from Addresses
alter table #Addresses add PK int identity(1, 1) primary key
select Name, Street, City, State
-- Explicitly name columns here to not return the PK
from #Addresses A
where not exists
(select *
from #Addresses B
where B.Name = A.Name
and A.PK > B.PK)
This solution would not be advisable for much larger tables.
对于更大的表，不建议使用此解决方案。
回答by A-K
select Name , street,city,state FROM( select Name , street,city,state, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name) AS rn from table) AS t WHERE rn=1
select Name , street,city,state FROM( select Name , street,city,state, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name) AS rn from table) AS t WHERE rn=1
回答by tekBlues
select distinct Name , street,city,state
from table t1 where street =
(select min(street) from table t2 where t2.name = t1.name)
回答by Robin Day
A temporary table solution would be as follows
临时表解决方案如下
CREATE Table #Addresses
(
MyId int IDENTITY(1,1),
[Name] NVARCHAR(50),
Street NVARCHAR(50),
City NVARCHAR(50),
State NVARCHAR(50)
)
INSERT INTO #Addresses ([Name], Street, City, State) SELECT [Name], Street, City, State FROM Addresses
SELECT
Addresses1.[Name],
Addresses1.Street,
Addresses1.City,
Addresses1.State
FROM
#Addresses Addresses1
WHERE
Addresses1.MyId =
(
SELECT
MIN(MyId)
FROM
#Addresses Addresses2
WHERE
Addresses2.[Name] = Addresses1.[Name]
)
DROP TABLE #Addresses
回答by Joe Davis
This is ugly as hell, but it sounds like your predicament is ugly, too... so here goes...
这太丑了，但听起来你的困境也很丑......所以这里......
select name,
(select top 1 street from [Addresses] a1 where a1.name = a0.name) as street,
(select top 1 city from [Addresses] a2 where a2.name = a0.name) as city,
(select top 1 state from [Addresses] a3 where a3.name = a0.name) as state
from (select distinct name from [Addresses]) as a0
回答by Jamie Ide
I think this is a good candidate for a cursor based solution. It's been so long since I've used a cursor that I won't attempt to write the T-SQL but here's the idea:
我认为这是基于游标的解决方案的一个很好的候选者。我已经很久没有使用游标了，所以我不会尝试编写 T-SQL，但我的想法是：
- Create temp table with same schema as Addresses
- Select distinct Names into cursor
- Loop through cursor selecting top 1 from Addresses into temp table for each distinct Name
- Return select from temp table
- 创建与地址相同架构的临时表
- 选择不同的名称到光标中
- 遍历光标从地址中选择前 1 个到每个不同名称的临时表中
- 从临时表中返回选择
回答by johnnycrash
And still another way:
还有另一种方式：
-- build a sample table
DECLARE @T TABLE (Name VARCHAR(50),Street VARCHAR(50),City VARCHAR(50),State VARCHAR(50))
INSERT INTO @T
SELECT 'Bob','123 Fake Street','Peoria','IL' UNION
SELECT 'Bob','234 Other Street','Fargo','ND' UNION
SELECT 'Jim','345 Main Street','St Louis','MO' UNION
SELECT 'Fred','234 Other Street','Fargo','ND'
-- here is all you do to get the unique record
SELECT * FROM @T a WHERE (SELECT COUNT(*) FROM @T b WHERE a.Name = b.name and a.street <= b.street) = 1
回答by Kinfo
select c.*, b.* from companies c left outer join
(SELECT *,
ROW_NUMBER()
OVER(PARTITION BY FKID ORDER BY PKId) AS Seq
FROM Contacts) b on b.FKID = c.PKID and b.Seq = 1
