如果存在配置，则选择第一行，否则选择NULL行

Question

给定一个SQL Server 2008实例，假设有一个名为@Configuration的表，其中有三列:ID、Code和SubCode。Code和SubCode不应该有重复的行。

现在想象另一个细节级表@ConfigurationDetails，它有Code和子代码的重复行，SubCode可以作为Null使用。

如果SubCode可用，则直接从详细表中选择Amt和Data，如果SubCode在详细表中不可用，则从NULL记录中选择Amt和Data

(注意:SubCode=NULL条目总是对每个配置行可用)

你知道从哪里开始吗?

。一个简单的例子……表

declare @Configuration TABLE ( 
      ID INTEGER IDENTITY PRIMARY KEY,
      Code VARCHAR(50),
      SubCode VARCHAR(50)

);
declare @ConfigurationDetails TABLE 
( 
    Code VARCHAR(50),
    SubCode VARCHAR(50),
    Amt MONEY,
    Data VARCHAR(123)
);

INSERT INTO @Configuration VALUES
('BR1','Sub1'),
('BR1','Sub2'),
('BR1','Sub3'),
('BR1','Sub4'),
('BR2','Sub1'),
('BR2','Sub2')
INSERT INTO @ConfigurationDetails VALUES
('BR1','Sub1',500,'BR1 Sub1 Data'),
('BR1','Sub2',600,'BR1 Sub2 Data'),
('BR1',NULL,700,'BR1 Data'),
('BR2','Sub1',500,'BR2 Sub1 Data'),
('BR2',NULL,700,'BR2 Data')

INPUT:
@SubCode  = 'Sub1', @Code = 'BR1'
OUTPUT:
Code    SubCode Amt Data
====    ======= === ====
BR1     Sub1    500 BR1 Sub1 Data
INPUT:
@SubCode  = 'Sub4', @Code = 'BR1'
OUTPUT:
Code    SubCode Amt Data
====    ======= === ====
BR1     NULL    700 BR1 Data

Answer 1

您应该能够使用像

这样的内容

SELECT *
FROM   @Configuration c
       CROSS APPLY (SELECT TOP 1 *
                    FROM   @ConfigurationDetails cd
                    WHERE  c.Code = cd.Code
                           AND ( c.SubCode = cd.SubCode
                                  OR c.SubCode IS NULL )
                    ORDER  BY cd.SubCode DESC --Order the not null match first if it exists
                   ) CA 

Answer 2

WITH cte
AS (
        SELECT  a.ID, 
                a.Code, 
                a.SubCode, 
                b.SubCode as bSubCode, 
                b.Amt, 
                b.Data,
                ROW_NUMBER() OVER(PARTITION BY a.CODE, a.SubCode ORDER BY B.SUBCODE desc) as RN
          FROM  @Configuration as a
          LEFT  JOIN @ConfigurationDetails as b ON b.Code = a.Code
                AND (a.SubCode = b.SubCode OR b.SubCode IS NULL)
        )
        SELECT * FROM cte
        where rn=1