查询保留最近的非零值

Question

我使用SQL Server 2016，并有一个表，其中包含以下数据:

TransMaxValue2505000400030050000

Answer 1

一种简单的方法是使用OUTER APPLY和SELECT TOP(1)。像这样

select m.PlaySeq, m.[TransMaxValue],
case when m.[TransMaxValue]=0 
then oa.TransMaxValue 
else m.TransMaxValue end TransMaxValueContd
from myTable m
outer apply (select top(1) mm.[TransMaxValue]
from myTable mm
where m.PlaySeq>mm.PlaySeq
and mm.[TransMaxValue]>0
order by mm.PlaySeq desc) oa;

PlaySeq TransMaxValue   TransMaxValueContd
1       250             250
2       500             500
3       0               500
4       400             400
5       0               400
6       300             300
7       500             500
8       0               500
9       0               500

Answer 2

也许这会有所帮助:

DECLARE @Tab TABLE(PlaySeq INT, TransMaxValue INT)
INSERT @Tab
VALUES(1,250),(2,500),(3,0),(4,400),(5,0)
,(6,300),(7,500),(8,0),(9,0)
SELECT PlaySeq, 
TransMaxValue,
FIRST_VALUE(TransMaxValue) OVER(PARTITION BY Grp ORDER BY PlaySeq) AS TransMaxValueContd
FROM (
SELECT
PlaySeq,
TransMaxValue,
SUM(CASE WHEN TransMaxValue = 0 THEN 0 ELSE 1 END) OVER(ORDER BY PlaySeq) AS Grp
FROM @Tab
) AS tbl
ORDER BY PlaySeq

Answer 3

请尝试以下解决方案。

基于Windows函数ROW_NUMBER()和不相等关系子句:

FROM @tbl AS t1 INNER JOIN 
@tbl AS t2 ON t1.PlaySeq > t2.PlaySeq

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (PlaySeq INT, TransMaxValue INT);
INSERT INTO @tbl (PlaySeq, TransMaxValue) VALUES
(1, 250),
(2, 500),
(3, 0  ),
(4, 400),
(5, 0  ),
(6, 300),
(7, 500),
(8, 0  ),
(9, 0  );
-- DDL and sample data population, end
;WITH rs AS
(
SELECT t1.*
, t2.TransMaxValue AS prev_value
, ROW_NUMBER() OVER (PARTITION BY t1.PlaySeq ORDER BY t2.PlaySeq DESC) AS seq
FROM @tbl AS t1 INNER JOIN 
@tbl AS t2 ON t1.PlaySeq > t2.PlaySeq
WHERE t2.TransMaxValue > 0
)
SELECT PlaySeq, TransMaxValue, TransMaxValue AS TransMaxValueContd
FROM @tbl WHERE PlaySeq = 1
UNION ALL
SELECT PlaySeq, TransMaxValue, IIF(TransMaxValue > 0, TransMaxValue, prev_value) AS TransMaxValueContd
FROM rs
WHERE seq = 1
ORDER BY PlaySeq ASC;

+---------+---------------+--------------------+
| PlaySeq | TransMaxValue | TransMaxValueContd |
+---------+---------------+--------------------+
|       1 |           250 |                250 |
|       2 |           500 |                500 |
|       3 |             0 |                500 |
|       4 |           400 |                400 |
|       5 |             0 |                400 |
|       6 |           300 |                300 |
|       7 |           500 |                500 |
|       8 |             0 |                500 |
|       9 |             0 |                500 |
+---------+---------------+--------------------+

Answer 4

可能有更好的方法，但您可以尝试使用相关子查询:

SELECT q.PlaySeq, q.TransMaxValue
, (CASE 
WHEN q.TransMaxValue <> 0 THEN q.TransMaxValue 
ELSE (SELECT d.TransMaxValue FROM myTable d WHERE d.PlaySeq = q.PlaySeqRef) 
END) TransMaxValueContd
FROM (
SELECT PlaySeq
, TransMaxValue
, (SELECT MAX(PlaySeq) 
FROM myTable b 
WHERE b.PlaySeq < a.PlaySeq 
AND b.TransMaxValue <> 0) PlaySeqRef
FROM myTable a) q;