sql - 如何用同一列中的最后一个 NOT NULL 值替换结果集中的 NULL？

Question

我的一个同事有一个 sql 查询问题:-

以下面两个临时表为例:-

select 'John' as name,10 as value into #names
UNION ALL SELECT 'Abid',20 
UNION ALL SELECT 'Alyn',30 
UNION ALL SELECT 'Dave',15;

select 'John' as name,'SQL Expert' as job into #jobs
UNION ALL SELECT 'Alyn','Driver' 
UNION ALL SELECT 'Abid','Case Statement';

我们对表运行以下查询以提供连接的结果集:-

select #names.name, #names.value, #jobs.job
FROM #names left outer join #jobs
on #names.name = #jobs.name

name    value    job
John    10       SQL Expert
Abid    20       Case Statement
Alyn    30       Driver
Dave    15       NULL

由于#jobs 表中不存在“Dave”，因此按预期为他提供了 NULL 值。

我的同事想要修改查询，以便为每个 NULL 值赋予与前一个条目相同的值。

所以以上将是:-

name    value    job
John    10       SQL Expert
Abid    20       Case Statement
Alyn    30       Driver
Dave    15       Driver

请注意，戴夫现在是“司机”

序列中可能有多个 NULL 值，

name    value    job
John    10       SQL Expert
Abid    20       Case Statement
Alyn    30       Driver
Dave    15       NULL
Joe     15       NULL
Pete    15       NULL

在这种情况下，Dave、Joe 和 Pete 都应该是“Driver”，因为“Driver”是最后一个非空条目。

Answer 1

可能有更好的方法来做到这一点。这是我可以使用 Common Table Expressions (CTE) 获得结果的方法之一并使用该输出执行 OUTER APPLY找到以前的人的工作。这里的查询使用 id对记录进行排序，然后确定前一个人的工作是什么。您至少需要一个条件来对记录进行排序，因为表中的数据被视为无序集。

此外，假设序列中的第一个人应该有工作。如果第一个人没有工作，那么就没有可供选择的值(value)。

Click here to view the demo in SQL Fiddle.

Click here to view another demo in SQL Fiddle with second data set.

脚本:

 CREATE TABLE names
    (
            id      INT         NOT NULL IDENTITY
      ,     name    VARCHAR(20) NOT NULL
      ,     value   INT         NOT NULL
    );

    CREATE TABLE jobs
    (
            id  INT         NOT NULL
      ,     job VARCHAR(20) NOT NULL
    );

    INSERT INTO names (name, value) VALUES
      ('John', 10),
      ('Abid', 20),
      ('Alyn', 30),
      ('Dave', 40),
      ('Jill', 50),
      ('Jane', 60),
      ('Steve', 70);

    INSERT INTO jobs (id, job) VALUES
      (1, 'SQL Expert'),
      (2, 'Driver' ),
      (5, 'Engineer'),
      (6, 'Barrista');

    ;WITH empjobs AS
    (
        SELECT
        TOP 100 PERCENT n.id
                    ,   n.name
                    ,   n.value
                    ,   job
        FROM            names n 
        LEFT OUTER JOIN jobs j
        on              j.id = n.id
        ORDER BY        n.id
    ) 
    SELECT      e1.id
            ,   e1.name
            ,   e1.value
            ,   COALESCE(e1.job , e2.job) job FROM empjobs e1
    OUTER APPLY (
                  SELECT 
                  TOP 1     job 
                  FROM      empjobs     e2
                  WHERE     e2.id   < e1.id
                  AND       e2.job  IS NOT NULL
                  ORDER BY  e2.id   DESC
                ) e2;

输出:

ID  NAME    VALUE  JOB
--- ------  -----  -------------
1   John      10   SQL Expert
2   Abid      20   Driver
3   Alyn      30   Driver
4   Dave      40   Driver
5   Jill      50   Engineer
6   Jane      60   Barrista
7   Steve     70   Barrista