如何从带有条件的多个地址中选择一个地址

Question

我有这样的桌子：

CustomerID | Address  | Address Type
100000001  | addressA | Mailing Address
100000001  | addressB | Registered Address
100000001  | Null.    | Office Address

一个客户可以有多个地址。我只想选择一个有条件的地址：

1. 如果Office地址不是null，那么我将从Office地址获得地址。如果Office地址为null，则转到2号。
2. 检查注册地址是否为空，然后我将从注册地址获得地址。

Answer 1

CREATE TABLE CUSTOMER (CUSTOMERID INT , ADDRESS VARCHAR(500) , ADDRESSTYPE VARCHAR(25));

INSERT INTO CUSTOMER VALUES (100000001,'ADDRESSA' ,'MAILING ADDRESS');
INSERT INTO CUSTOMER VALUES (100000001,'ADDRESSB',  'REGISTERED ADDRESS');
INSERT INTO CUSTOMER VALUES (100000001, NULL ,'OFFICE ADDRESS');

SELECT 
    ISNULL(
        (SELECT ADDRESS FROM CUSTOMER WHERE CUSTOMERID = 100000001 AND ADDRESSTYPE = 'OFFICE ADDRESS' ) ,
        (SELECT ADDRESS FROM CUSTOMER WHERE CUSTOMERID = 100000001 AND ADDRESSTYPE = 'REGISTERED ADDRESS' )
    ) 

Answer 2

select * from customer 

select  max(customerID) customerID ,max(Address) Address ,max(Addresstype) Addresstype
from customer  
where address is not null
group by customerID

输出

​

​