如果下一行中的下一个值为空，则选择行 [英] Select row if next a value in next row is null

问题描述

如果下一行的特定列的值为空，我需要知道如何选择一行.
例如:

I need to know how to select a row if the next row has a null value for specific column.
For example:

Ind      | Opt50    | Stat
44       | 1        | NR
45       | 1        | CL    
46       | NULL     | NULL   
47       | 1        | IE    
48       | NULL     | NULL    
49       | NULL     | NULL   
50       | 1        | NR  
51       | 1        | IE
52       | 1        | CL
53       | 1        | IE    
54       | NULL     | NULL

---

如果下一条记录的状态为"IE"且Option50为NULL，那么我需要返回该下一条记录.对于上表，我需要查询以查找ind 47和53并返回ind 48和54.

---

If Status is 'IE' and Option50 is NULL for the next record, then I need to return that next record. For the table above I need the query to find ind 47 and 53 and to return ind 48 and 54.

Ind      | Opt50    | Stat  
48       | NULL     | NULL
54       | NULL     | NULL

---

我正在使用SQL Server 2008. 有任何想法吗?

---

I am using SQL Server 2008. Any ideas?

推荐答案

假定列Ind是相关的，则:

SELECT  B.*
FROM dbo.YourTable A
INNER JOIN dbo.YourTable B
    ON A.Ind = B.Ind - 1
WHERE A.Stat = 'IE' 
AND B.Opt50 IS NULL

这是一个 sqlfiddle，上面有一个演示

Here is a sqlfiddle with a demo of this.

结果是:

╔═════╦════════╦════════╗
║ Ind ║ Opt50  ║  Stat  ║
╠═════╬════════╬════════╣
║  48 ║ (null) ║ (null) ║
║  54 ║ (null) ║ (null) ║
╚═════╩════════╩════════╝

如果Ind中有空格，则可以执行以下操作:

In the case that Ind has gaps in it, then you can do:

;WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(ORDER BY Ind)
    FROM dbo.YourTable
)

SELECT  B.*
FROM CTE A
INNER JOIN CTE B
    ON A.RN = B.RN - 1
WHERE A.Stat = 'IE' 
AND B.Opt50 IS NULL

这是此版本的 sqlfiddle.

Here is a sqlfiddle with this version.

这篇关于如果下一行中的下一个值为空，则选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！