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

好吧,这会给你带来相当糟糕的表现,但我认为它会起作用的

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

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

如果可以使用临时表:

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)

对于更大的表来说,这个解决方案是不可取的。

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

从中选择名称、街道、城市、州( 选择名称、街道、城市、州, 行_number()覆盖(按名称分区,按名称排序)为rn 从表中)作为t 其中Rn=1

这是地狱般的丑陋,但听起来你的困境也是丑陋的…所以这里…

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

临时表解决方案如下

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

我认为这是基于光标的解决方案的一个很好的候选者。我用光标已经很久了,所以我不想写T-SQL,但我的想法是:

1. 使用与地址相同的架构创建临时表
2. 在光标中选择不同的名称
3. 循环通过光标选择每个不同名称的第一个地址到临时表中
4. 从临时表返回选择

考虑到你的限制,我认为你做不到。您可以提取这些字段的不同组合。但是如果有人用同一个地址拼写鲍勃和鲍伯,你会得到两个记录。[gigo]正确的做法是,任何分组(所有字段上的分组都不等于distinct)都将混合行。可惜的是,您没有每个客户的唯一标识符。

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

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

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

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

还有另一种方式:

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

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

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;