sql puzzle sql server 2000

Question

我有一个小难题。PK是lineNr和typeNr的组合我想做出一个选择，其中所有is_adres都是 N，所有亚麻布都一样，is_postaddres也是如此。我希望结果在一个视图中。

这是我想要的最终结果

typenr,is_Adres ,is_postaddres 
10, null , 'N'
11, 'N', null
13, 'N', 'N'

创建表脚本 + 测试数据

CREATE TABLE lineAdres 
( 
 lineNr int,
 typeNr int, 
 is_Adres char(1),
 is_postaddres char(1)
);
INSERT INTO lineAdres VALUES
(1, 10,'J','N'),
(1, 11,'N','J'),
(1, 12,'N','J'),
(1, 13,'N','N'),
(2, 10,'J','N'),
(2, 11,'N','J'),
(2, 12,'J','N'),
(2, 13,'N','N');

我想将这 2 个视图合二为一.. 但是如何，我有一种感觉它可以更有效率！

select typenr, is_adres 
from lineAdres
where is_adres = 'N'
group by typenr, is_adres
having count(*) = 2
select typenr, is_postaddres 
from lineAdres
where is_postaddres = 'N'
group by typenr, is_postaddres
having count(*) = 2

Answer 1

要联接这两个结果，请使用 CTE 和完全联接来合并数据。

WITH IsAddress AS(
select typenr, is_adres 
from lineAdres
where is_adres = 'N'
group by typenr, is_adres
having count(*) = 2),

IsPostAddress AS (select typenr, is_postaddres 
from lineAdres
where is_postaddres = 'N'
group by typenr, is_postaddres
having count(*) = 2)
SELECT 
       COALESCE(IsAddress.typenr,IsPostAddress.typenr) typenr,
       IsAddress.IsAddress,
       IsPostAdress.is_postaddres
FROM
       IsAddress
       FULL OUTER JOIN
       IsPostAdress
            ON
            IsAddress.typenr = IsPostAdress.typenr