gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-02 04:20:51 24 4
gpt4 key购买 nike

我有一个看起来像这样的表格:

enter image description here

可以通过以下代码重新创建:

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 来填充它。

表格应如下所示:

enter image description here

对于我这边缺乏代码尝试表示歉意。我不知道从哪里开始。任何指示都将不胜感激。

最佳答案

有多种方法可以解决这个问题。一种是应用。

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

关于sql - TSQL - 通过引用其他列来填充条目上方和下方的值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38099582/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com