MS SQL 2008年加入 - 选择一个从许多成果(MS SQL 2008 join - sele

我试图运行下面的查询，但我不知道如何将它限制到只有一个结果。 在下面的查询中，客户端clientcontactid 21901件作品有2个地址，这意味着2个结果返回。

查询：

select  cc.contactpersonid,
    cc.clientcontactid,
    ad.city,
    ad.addressid
from SavedList sl
inner join ClientContacts cc on cc.ContactPersonId = sl.ObjectId
inner join Clients c on c.ClientID = cc.ClientId
inner join Address ad on c.ClientID = ad.ObjectId
where sl.SavedListId = 2117

结果：

contactpersonid clientcontactid city    addressid
87934           21901                   145186
87934           21901           London  1130705
89778           17275           Leeds   145368

我需要返回的结果为客户联系21901的一个，则优先级为上一个与城市的它。 我试过选择顶（1），但我认为这是倒在加盟迫使多个记录回来。 如何只返回1结果任何帮助，以及如何控制，将不胜感激！

谢谢

Answer 1:

尝试：

;WITH a AS (
select  cc.contactpersonid,
    cc.clientcontactid,
    ad.city,
    ad.addressid,
    ROW_NUMBER() OVER (PARTITION BY cc.clientcontactid ORDER BY ad.city DESC) AS RowNum
    from SavedList sl
    inner join ClientContacts cc on cc.ContactPersonId = sl.ObjectId
    inner join Clients c on c.ClientID = cc.ClientId
    inner join Address ad on c.ClientID = ad.ObjectId
    where sl.SavedListId = 2117
)

SELECT  *
FROM    a
WHERE   RowNum = 1

Answer 2:

我通常做优先考虑的结果，是实际分配优先值列。 在这种情况下，它可以保持简单，因为只有一个重点：随着城市的记录来没有城市之前：

with q as(
select  cc.contactpersonid,
    cc.clientcontactid,
    ad.city,
    ad.addressid,
    case when ad.city is null then 0 else 1 end prior
from SavedList sl
inner join ClientContacts cc on cc.ContactPersonId = sl.ObjectId
inner join Clients c on c.ClientID = cc.ClientId
inner join Address ad on c.ClientID = ad.ObjectId
where sl.SavedListId = 2117
)
select top 1 * from q order by prior desc

Answer 3:

大，但它的工作原理：

select  cc.contactpersonid,
        cc.clientcontactid,
        ad.city,
        ad.addressid
from SavedList sl
inner join ClientContacts cc on cc.ContactPersonId = sl.ObjectId
inner join Clients c on c.ClientID = cc.ClientId
inner join Address ad on c.ClientID = ad.ObjectId
where sl.SavedListId = 2117 and (ad.city is not null or ad.city <> '')
UNION
select  cc.contactpersonid,
        cc.clientcontactid,
        ad.city,
        ad.addressid
from SavedList sl
inner join ClientContacts cc on cc.ContactPersonId = sl.ObjectId
inner join Clients c on c.ClientID = cc.ClientId
inner join Address ad on c.ClientID = ad.ObjectId
where sl.SavedListId = 2117 and (ad.city is null or ad.city = '')
and cc.clientcontactid not in (
  select  cc.clientcontactid
  from SavedList sl
  inner join ClientContacts cc on cc.ContactPersonId = sl.ObjectId
  inner join Clients c on c.ClientID = cc.ClientId
  inner join Address ad on c.ClientID = ad.ObjectId
  where sl.SavedListId = 2117 and (ad.city is not null or ad.city <> '')
)

Answer 4:

你可以尝试select top 1 record加入的条件，其中city is not NULL 。

Answer 5:

尝试这个：

;WITH cte
     AS (SELECT cc.contactpersonid,
                cc.clientcontactid,
                ad.city,
                ad.addressid,
                Row_number() OVER (PARTITION BY cc.clientcontactid ORDER BY CASE WHEN ad.city <> '' THEN 1 ELSE 0 END) rn
         FROM   SavedList sl
                INNER JOIN ClientContacts cc
                  ON cc.ContactPersonId = sl.ObjectId
                INNER JOIN Clients c
                  ON c.ClientID = cc.ClientId
                INNER JOIN Address ad
                  ON c.ClientID = ad.ObjectId
         WHERE  sl.SavedListId = 2117)
SELECT contactpersonid,
       clientcontactid,
       city,
       addressid
FROM   cte
WHERE  rn = 1

文章来源: MS SQL 2008 join - select one from many results