查询以获取具有AddressType one AddressType 2的地址列表？

Question

考虑一下下面的表格

Id  PersonId    Address   AddressTypeId
--------------------------------------------------------------------
1     1         AI1P1T1      1
2     1         AI2P1T2      2  
3     2         AI3P2T2      2

我想写一个查询来打印拥有AddressType =1或AddressTypeId=2的人的地址列表，并且

如果person有AddressType =1，则选择它，否则选择person with AddressType =2

预期结果：

Address
--------------
AI1P1T1           
AI3P2T2   

Answer 1

日安，

请检查这是否解决了您的需求：

/***************************** DDL+DML */
drop table if exists T;
create table T(Id int,PersonId int, [Address] nvarchar(10), AddressTypeId int)
INSERT T(Id,PersonId, [Address], AddressTypeId)
values
(1,1,'AI1P1T1',1),
(2,1,'AI2P1T2',2),
(3,2,'AI3P2T2',2)
GO

select * from T
GO

/***************************** Solution */
With MyCTE as (
    select *, ROW_NUMBER() OVER (partition by PersonId order by AddressTypeId) as RN
    from T
)
select [Address]
from MyCTE
where 
    AddressTypeId in (1,2) -- if there can be only positive numbers then you can use "< 3"
    and RN = 1
GO

Answer 2

您也可以尝试使用joins：

 select t1.PersonId,t1.Address from #T t1 
  inner join (select personid,min(AddressTypeId)atype from #T
  group by PersonId )x 
  on x.atype=t1.AddressTypeId and x.PersonId=t1.PersonId

Answer 3

我会写一个子查询，通过窗口函数生成ROW_NUMBER，然后在主查询中使用MAX。

SELECT 
    PersonId, MAX(Address) Address
FROM 
    (SELECT
         PersonId,
         (CASE 
             WHEN ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY PersonId) = 1 
                THEN Address 
          END) Address
     FROM 
         T
     WHERE 
         AddressTypeId IN (1,2)
    ) t1
GROUP BY 
    PersonId

sqlfiddle

结果

| PersonId | Address |
+----------+---------+
|        1 | AI1P1T1 |
|        2 | AI3P2T2 |