SQL query to choose row before two consecutive tru

问题:

Here is my table.

Price   Volume
------------------
60          0
70          10
80          0
90          0
100         40
200         40
300         40
400         0
500         50
600         60

It is ordered by Price.

I need to choose all rows before two consecutive zeros in Volume column. So the result should look like

Price   Volume
------------------
100         40
200         40
300         40
400         0
500         50
600         60

I really do not know where to start with this type of query. I could only think go pulling data into C# and the loading back into table.

Regards,

回答1:

You didn't state your DBMS so this is ANSI SQL:

with flagged as (
  select price, 
         volume,
         case 
           when volume + lag(volume) over (order by price) = 0 then 1
           else 0
         end as rn
  from prices
), counted as (
  select price, 
         volume, 
         sum(rn) over (order by price) as cn,
         rn
  from flagged
)
select price, volume
from counted
where cn > 0 and rn = 0
order by price

Here is a SQLFiddle example: http://sqlfiddle.com/#!6/b2434/4

回答2:

Standard SQL cannot handle this. You need a cursor to cycle through the ordered rows and decide what to output, which requires the use of some variables.

Depending on the RDBMS you are using, you could write a stored procedure that reads the rows and filters them. Otherwise you must have the application do the filtering.

回答3:

In sql server 2012+ you can do this with a couple LAG statements.

Lag for the 2 previous values and where when they are both 0.