选择符合条件的所有记录以及与条件不匹配的下一条记录

I've got a table that has columns like this:

我有一个包含这样的列的表:

ID, Description, PC, Account, Amount

I'm going to be looking for a 009 value in the "PC" column, and each datum has a sequentially-numbered unique ID in the "ID" column. Every datum with a 009 "PC" value will have at least one item following it that doesn't have "009" as a PC value.

我将在“PC”列中查找009值,并且每个数据在“ID”列中都有一个顺序编号的唯一ID。具有009“PC”值的每个数据将在其后面至少有一个项目没有“009”作为PC值。

Is there any way I can write a query that's real and similar to the fake code below? Is something like this actually possible?

有没有什么办法可以编写一个真实的,类似于下面的假代码的查询?这样的事情真的可能吗?

SELECT  
table.ID AS ID,  
table.Description AS Description,  
table.PC AS PC
WHERE ((table.PC = 009) OR (whatever, as long as table.ID-1's "PC" value = 009))

3 个解决方案

#1

If you know the ids are really sequential, you can put the logic in the WHERE clause:

如果你知道id确实是顺序的,你可以把逻辑放在WHERE子句中:

SELECT t.ID, t.Description, t.PC AS PC
FROM table as t
WHERE t.PC = 009 OR
      EXISTS (SELECT 1
              FROM table as t2
              WHERE t2.id = t.id - 1 AND t2.PC = 009
             )
ORDER BY t.ID;

#2

SELECT  
table.ID AS ID,  
table.Description AS Description,  
table.PC AS PC
FROM table
WHERE ((table.PC = '009') OR 
table.ID - 1 = 
  (
  SELECT table.ID
  FROM table
  WHERE table.PC = '009'
  )
)

#3

SELECT  
    t.ID AS ID,  
    t.Description AS Description,  
    t.PC AS PC
FROM tbl t
WHERE 
   (t.PC = 009) OR 
    (t.id - 1 = (SELECT t2.ID FROM tbl t2 where t.PC = 009))