SQL 查询为列中的每个唯一值返回一条记录

Question

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

看吧，[Addresses]：

Name         Street                 City          State
--------------------------------------------------------
Bob          123 Fake Street        Peoria        IL
Bob          234 Other Street       Fargo         ND
Jim          345 Main Street        St Louis      MO

这实际上是实际表结构的简化示例。 表的结构完全超出我的控制范围。 我需要一个查询，该查询将为每个名称返回一个地址。 哪个地址并不重要，重要的是只有一个地址。 结果可能是这样的：

Name         Street                 City          State
--------------------------------------------------------
Bob          123 Fake Street        Peoria        IL
Jim          345 Main Street        St Louis      MO

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

更改表结构的建议对我没有帮助。 我同意这个表很糟糕（它比这里显示的更糟糕），但这是我无法更改的主要 ERP 数据库的一部分。

该表大约有3000条记录。 没有主键。

有任何想法吗？

Answer 1

选择名称、街道、城市、州 FROM(
选择名称、街道、城市、州、
ROW_NUMBER() OVER(PARTITION BY 名称 ORDER BY 名称) AS rn
从表）AS t
其中 rn=1

Answer 2

这太丑陋了，但听起来你的困境也很丑陋......所以这里......

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

Answer 3

临时表解决方案如下

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

Answer 4

我认为这是基于游标的解决方案的一个很好的候选者。 我已经很久没有使用游标了，所以我不会尝试编写 T-SQL，但想法如下：

1. 创建与 Addresses 具有相同架构的临时表
2. 将不同的名称选择到游标中
3. 循环遍历游标，从 Addresses 中选择前 1 个每个不同名称的临时表
4. 返回从临时表中选择

Answer 5

考虑到你的限制，我认为你无法做到这一点。 您可以提取这些字段的不同组合。 但如果有人用相同的地址拼写 Bob 和 Bobb，您最终会得到两条记录。 [GIGO] 您是正确的，任何分组（缺少对所有字段进行分组 - 相当于 DISTINCT）都会混合行。 遗憾的是您没有为每个客户提供唯一的标识符。

您可以将查询嵌套在一起，例如为每个名称选择前 1 个并将所有这些连接在一起。

Answer 6

对上面的内容稍作修改就可以了。

SELECT Name, Street, City, State
FROM table t 
INNER JOIN (
     SELECT Name, MIN(Street) AS Street
     FROM table m
     GROUP BY Name
) x
   ON x.Name = t.Name AND x.Street = t.Street

现在，如果您有相同的街道但其他信息不同（例如有拼写错误），则这将不起作用。

或者更完整的散列将包括所有字段（但对于性能而言，您可能有太多字段）：

SELECT Name, Street, City, State
FROM table t 
INNER JOIN (
     SELECT Name, MIN(Street + '|' + City  + '|' + State) AS key
     FROM table m
     GROUP BY Name
) x
   ON  x.Name = t.Name
   AND x.key = Street + '|' + City  + '|' + State

Answer 7

SELECT name,
       ( SELECT TOP 1 street, city, state
           FROM addresses b
          WHERE a.name = b.name )
  FROM addresses a
 GROUP BY name

Answer 8

还有另一种方式：

-- 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

Answer 9

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

Answer 10

SELECT name, street, address, state
FROM
 (SELECT name, street, address, state,
  DENSE_RANK() OVER (PARTITION BY name ORDER BY street DESC) AS r 
 FROM tbl) AS t
WHERE r = 1; 

Answer 11

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

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

Answer 12

使用临时表或表变量并在其中选择不同的名称列表。 然后使用该结构为每个不同的名称选择原始表中每条记录的前 1 条。

Answer 13

如果可以使用临时表：

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)

对于更大的表，不建议使用此解决方案。

Answer 14

select distinct Name , street,city,state
from table t1 where street =  
(select min(street) from table t2 where t2.name = t1.name)