前端之家

  1. 首页
  2. 问答

返回空值之前的前一个值

• 问答

我当前正在查询缺少索引的表。

以下是一些示例数据：

id  dStartDate
126 2010-04-22 00:00:00.000
127 NULL
128 2010-04-29 00:00:00.000
129 2010-05-03 00:00:00.000
130 NULL
131 NULL
132 NULL
133 2010-04-29 00:00:00.000
134 NULL
135 NULL
136 2010-04-29 00:00:00.000
137 NULL
138 NULL
139 2010-04-29 00:00:00.000
140 NULL
141 2010-04-29 00:00:00.000
142 2010-04-29 00:00:00.000
143 NULL
144 NULL

我使用以下脚本获取缺少的索引：

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(idJCMaster) from _btblJCMaster

declare @IDseq table    (id int)

while @id < @maxid --whatever you max is
begin
    insert into @IDseq values(@id)

    set @id = @id + 1
end

select 
    s.id
from        @IDseq s 
left join   _btblJCMaster t on s.id = t.idJCMaster
where t.idJCMaster is null

上面的方法很完美，但是，我想查看以前的记录（不为空）的日期，以了解何时删除该记录...

我更改了上面的脚本，使其看起来像这样：

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(idJCMaster) from _btblJCMaster

declare @IDseq table    (id int)

while @id < @maxid --whatever you max is
begin
    insert into @IDseq values(@id)

    set @id = @id + 1
end

select 
    s.id,t.dStartDate
from        @IDseq s 
left join   _btblJCMaster t on s.id = t.idJCMaster

我得到的结果如下：

返回空值之前的前一个值

可以看出，有时对于那些特定索引而言，遗失的数量超过记录...

我不太确定如何更改脚本以显示以前的日期（空值之前）。

在此示例中，我的预期结果将是：

返回空值之前的前一个值

请协助取得预期结果？

非常感谢您的协助！

修改

在Ankit的帮助下，尝试了以下方法（他的回答）：

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(idJCMaster) from _btblJCMaster

declare @IDseq table    (id int)

while @id < @maxid --whatever you max is
begin
    insert into @IDseq values(@id)

    set @id = @id + 1
end

select 
    s.id,(SELECT MAX(dStartDate)
            FROM _btblJCMaster
            WHERE id >= t1.idJCMaster) dStartDate
from        @IDseq s 
left join   _btblJCMaster t1 on s.id = t1.idJCMaster

但是我仍然收到NULLS。

然后，我通过尝试更改LAG函数并添加LEAD和3个CTE来尝试他的第一个答案，但是我仍然得到NULLS：

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(idJCMaster) from _btblJCMaster

declare @IDseq table    (id int)

while @id < @maxid --whatever you max is
begin
    insert into @IDseq values(@id)

    set @id = @id + 1
end


;with cte (id,dStartDate,idJCMaster)
as
(
select 
    s.id,IsnULL(dStartDate,isnull(LAG(dStartDate) OVER(order by s.id),LEAD(dStartDate) OVER(order by s.id))),IdJCMaster
from        @IDseq s 
left join   _btblJCMaster t1 on s.id = t1.idJCMaster
),cte2 (id,idJCMaster)
as
(
select
    id,isnull(dStartDate,LAG(dStartDate) OVER(order by id)),idJCMaster
from    cte
),cte3 (id,LEAD(dStartDate) OVER(order by id)),idJCMaster
from    cte2
)

select
    id,LAG(dStartDate) OVER(order by id))
from    cte3
where   idJCMaster is null

还有没有其他更简单的方法可以做到这一点？

vcaonimav 回答：返回空值之前的前一个值

您可以在下面的查询中尝试-

SELECT id,(SELECT MAX(dStartDate)
            FROM YOUR_TABLE
            WHERE id >= t1.id) dStartDate
FROM YOUR_TABLE t1;
,

您可以尝试以下方法：

首先，我们需要一个模型表来模拟您的问题。请在下一个问题中自行提供。始终最好提供一个自运行的，独立的示例，其中包括DDL，INSERT和您自己的尝试。这样的模拟称为MCVE。

DECLARE @tbl TABLE(id INT,dStartDate DATE);
INSERT INTO @tbl VALUES
 (126,'2010-04-22 00:00:00.000'),(127,NULL),(128,'2010-04-29 00:00:00.000'),(129,'2010-05-03 00:00:00.000'),(130,(131,(132,(133,(134,(135,(136,(137,(138,(139,(140,(141,(142,(143,(144,NULL);

-查询

WITH cte AS(SELECT id,dStartDate FROM @tbl WHERE dStartDate IS NOT NULL)
SELECT t.id,A.gaplessStartDate
FROM @tbl t
CROSS APPLY(SELECT TOP 1 cte.dStartDate 
            FROM cte 
            WHERE cte.id<=t.id 
            ORDER BY cte.id DESC) A(gaplessStartDate);

简而言之：

我们首先使用CTE来获得仅包含非空行的集合。
现在，我们可以使用APPLY来获取拟合行以及ID，方法是调用降序排列的最顶部较小ID 。

该方法有点像triangle JOIN (Jeff Moden wrote a great article on this)。任何行都需要具有ORDER BY动作的相关子查询。

提示：如果使用索引临时表而不是CTE，则设置较大的值可能会更快。

,

谢谢@Shnugo的帮助！

在您的帮助下，以下脚本为我提供了我可以在数据集上正常使用的内容：

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(idJCMaster) from _btblJCMaster

declare @IDseq table    (id int)

while @id < @maxid --whatever you max is
begin
    insert into @IDseq values(@id)

    set @id = @id + 1
end

;with source (id,dStartDate)
as
(
select 
    s.id,dStartDate
from        @IDseq s 
left join   _btblJCMaster t1 on s.id = t1.idJCMaster
),cte AS(SELECT id,dStartDate FROM source WHERE dStartDate IS NOT NULL)
SELECT t.id,A.gaplessStartDate
FROM source t
CROSS APPLY(SELECT TOP 1 cte.dStartDate 
            FROM cte 
            WHERE cte.id<=t.id 
            ORDER BY cte.id DESC) A(gaplessStartDate)
WHERE t.dStartDate IS NULL
order by id

这仅供其他观众使用，如果您需要的话。

sql-server-2017tsql
本文链接：https://www.f2er.com/3060398.html

大家都在问