sql - TSQL-通过引用其他列来填充条目上方和下方的值

我有一张 table ，看起来像:



可以从下面的代码中重新创建它:

CREATE TABLE dbo.EmpnoProblem
(
DATE date NULL,
WORKNO nvarchar(50) NULL,
OPSEQ int NULL,
RELEASED nchar(10) NULL,
PRODUCT nvarchar(50) NULL,
EMPNO nvarchar(50) NULL
) ;

INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 10, '10', '5454ABC', NULL);
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 20, '10', '5454ABC', NULL);
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 30, '10', '5454ABC', '345');
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 40, '10', '5454ABC', '345');
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 50, '10', '5454ABC', NULL);
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 60, '10', '5454ABC', NULL);
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 70, '10', '5454ABC', '698');
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 80, '10', '5454ABC', NULL);
INSERT INTO [dbo].[EmpnoProblem] (DATE, WORKNO, OPSEQ, RELEASED, PRODUCT, EMPNO)
VALUES ('2016-06-16', '12345', 90, '10', '5454ABC', NULL);

我需要填充EMPNO列。

当3列相同时:WORKNO，RELEASED，PRODUCT，我正在处理一个单独的分组，而我的数据集由这些分组组成。

在此分组中，如果EMPNO为空，则首先需要使用下一个不为空的EMPNO向下填充(这可能是向下的几个条目)。下一个EMPNO下降由OPSEQ编号确定。如果没有EMPNO在分组中位于最下面的EMPNO(例如末尾的两个空条目)，则需要使用上面不为null的最后一个EMPNO填充它。

该表应如下所示:



抱歉，我没有代码尝试。我不确定从哪里开始。任何指针都将不胜感激。

最佳答案

有多种解决方法。一种是与APPLY。

select
    a.*,
    COALESCE(a.EMPNO,next_NONEMPTY_EMPNO.EMPNO,prev_NONEMPTY_EMPNO.EMPNO) EMPNO_Fixed
from #EmpnoProblem a
OUTER APPLY (
    select TOP 1
        EMPNO
    from #EmpnoProblem b
    where
        EMPNO is not null and
        a.WORKNO = b.WORKNO and
        a.RELEASED = b.RELEASED and
        a.PRODUCT = b.PRODUCT and
        b.OPSEQ > a.OPSEQ
    order by OPSEQ ASC
) next_NONEMPTY_EMPNO
OUTER APPLY (
    select TOP 1
        EMPNO
    from #EmpnoProblem b
    where
        EMPNO is not null and
        a.WORKNO = b.WORKNO and
        a.RELEASED = b.RELEASED and
        a.PRODUCT = b.PRODUCT and
        b.OPSEQ < a.OPSEQ
    order by OPSEQ DESC
) prev_NONEMPTY_EMPNO