SQL根据计数和/或连接表的标志选择标志

Question

我有一个Customer表和一个Address表。

地址表有一个标志，可以是INVOICE、CORRESPONDENCE或DELIVERY。

一个客户可以有0到多个Address记录。

我希望能够查询这两个表，并根据地址数据为每个客户生成一个标志-没有地址记录= NONE, 1个或多个INVOICE记录= HASINVOICE，没有INVOICE但1个或多个其他记录= HASOTHER

so，对于以下数据:

+------------+---------+
| CustomerID |  Name   |
+------------+---------+
|          1 | Peter   |
|          2 | Ray     |
|          3 | Egon    |
|          4 | Winston |
|          5 | Dana    |
+------------+---------+
+-----------+------------+----------------+
| AddressID | CustomerID |  AddressType   |
+-----------+------------+----------------+
|         1 |          1 | INVOICE        |
|         2 |          1 | DELIVERY       |
|         3 |          2 | DELIVERY       |
|         4 |          2 | CORRESPONDENCE |
|         5 |          4 | INVOICE        |
|         6 |          5 | CORRESPONDENCE |
+-----------+------------+----------------+

我希望得到以下输出:

+------------+---------+-------------+
| CustomerID |  Name   | AddressFlag |
+------------+---------+-------------+
|          1 | Peter   | HASINVOICE  |
|          2 | Ray     | HASOTHER    |
|          3 | Egon    | NONE        |
|          4 | Winston | HASINVOICE  |
|          5 | Dana    | HASOTHER    |
+------------+---------+-------------+

这是可能的，对于SQL 2000，使用单个查询和没有游标?

Answer 1

我手边没有2000个实例(你真的应该升级，你落后了4-5个版本)，但我认为这应该可以工作:

declare @Customers table (CustomerID int,Name varchar(10))
insert into @Customers (CustomerID,Name)
select 1,'Peter' union all    select 2,'Ray' union all
select 3,'Egon' union all    select 4,'Winston' union all
select 5,'Dana'
declare @Addresses table (AddressID int, CustomerID int,
                          AddressType varchar(30))
insert into @Addresses (AddressID,CustomerID,AddressType)
select 1,1,'INVOICE' union all    select 2,1,'DELIVERY' union all
select 3,2,'DELIVERY' union all    select 4,2,'CORRESPONDENCE' union all
select 5,4,'INVOICE' union all    select 6,5,'CORRESPONDENCE'
select
    c.CustomerID,
    c.Name,
    CASE MAX(CASE 
        WHEN a.AddressType = 'Invoice' THEN 2
        WHEN a.AddressType IS NOT NULL THEN 1
        END
    ) WHEN 2 THEN 'HASINVOICE'
    WHEN 1 THEN 'HASOTHER'
    ELSE 'NONE'
    END as AddressFlag
from
    @Customers c
        left join
    @Addresses a
        on
            c.CustomerID = a.CustomerID
group by
    c.CustomerID,
    c.Name

生产:

CustomerID  Name       AddressFlag
----------- ---------- -----------
5           Dana       HASOTHER
3           Egon       NONE
1           Peter      HASINVOICE
2           Ray        HASOTHER
4           Winston    HASINVOICE