SQL 查询为列中的每个唯一值返回一条记录 [英] SQL query to return one single record for each unique value in a column

问题描述

我在 SQL Server 2000 中有一个表，我试图以特定方式查询它.展示这一点的最佳方式是使用示例数据.

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.

看哪，[地址]:

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

我在这里找到了一个类似的问题，但是在我的情况下给出的解决方案都不起作用，因为我无权访问 CROSS APPLY，并且在每列上调用 MIN() 会将不同的地址混合在一起，虽然我不在乎返回哪条记录，但它必须是一个完整的行，而不是不同行的混合.

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.

更改表结构的建议对我没有帮助.我同意这张表很糟糕(比这里显示的更糟糕)但这是我无法更改的主要 ERP 数据库的一部分.

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.

这个表大约有 3000 条记录.没有主键.

There are about 3000 records in this table. There is no primary key.

有什么想法吗?

推荐答案

好吧，这会给你带来非常糟糕的性能，但我认为它会起作用

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

这篇关于SQL 查询为列中的每个唯一值返回一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！