SQL查询在两个连续的真值之前选择行 [英] SQL query to choose row before two consecutive true values

问题描述

这是我的桌子.

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

按价格排序.

我需要选择 Volume 列中两个连续零之前的所有行.所以结果应该是这样的

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

我真的不知道从哪里开始这种类型的查询.我只能想到将数据拉入 C# 并加载回表中.

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.

问候，

推荐答案

你没有说明你的 DBMS 所以这是 ANSI SQL:

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

这是一个 SQLFiddle 示例:http://sqlfiddle.com/#!6/b2434/4

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

这篇关于SQL查询在两个连续的真值之前选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！