oracle 选择所有行直到第一次出现给定值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/733979/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting all rows until first occurrence of given value
提问by Kamil Zadora
For following data:
对于以下数据：
date|value|check
2009 | 5 | 1
2008 | 5 | 1
2007 | 5 | 1
2006 | 5 | 0
2005 | 5 | 0
2004 | 5 | 1
2003 | 5 | 1
2002 | 5 | 1
日期|值|检查
2009 | 5 | 1
2008 | 5 | 1
2007 | 5 | 1
2006 | 5 | 0
2005 | 5 | 0
2004 | 5 | 1
2003 | 5 | 1
2002 | 5 | 1
I need to select all rows from 2009 back until first occurrence of 0 in check column:
我需要选择从 2009 年开始的所有行，直到检查列中第一次出现 0：
date|value|check
2009 | 5 | 1
2008 | 5 | 1
2007 | 5 | 1
日期|值|检查
2009 | 5 | 1
2008 | 5 | 1
2007 | 5 | 1
I tried with the lag function, but I was only able to check a month back.
我试过滞后功能，但我只能在一个月前检查。
I am working on Oracle 10g.
我正在研究 Oracle 10g。
UPDATE:
更新：
All seems to work well, my test data set is too small to say anything about the performance differences.
一切似乎都运行良好，我的测试数据集太小，无法说明性能差异。
回答by kenj0418
SELECT * FROM mytable where date > (
SELECT max(date) FROM mytable where check = 0
)
回答by Quassnoi
SELECT *
FROM (
SELECT m.*,
MIN(CASE WHEN check = 0 THEN 0 ELSE 1 END) OVER (ORDER BY date DESC)) AS mn
FROM mytable
)
WHERE mn = 1
or even better:
甚至更好：
SELECT *
FROM (
SELECT m.*, ROW_NUMBER() OVER (ORDER BY mydate DESC) AS rn
FROM mytable m
ORDER BY
mydate DESC
)
WHERE rownum = DECODE(check, 0, NULL, rn)
ORDER BY
mydate DESC
The latter query will actually stop scanning as soon as it encounters the first zero in check.
后一个查询实际上会在遇到检查中的第一个零时立即停止扫描。
回答by Lieven Keersmaekers
DECLARE @mytable TABLE (date integer, [value] integer, [check] integer)
INSERT INTO @mytable VALUES (2009, 5, 1)
INSERT INTO @mytable VALUES (2008, 5, 1)
INSERT INTO @mytable VALUES (2007, 5, 1)
INSERT INTO @mytable VALUES (2006, 5, 0)
INSERT INTO @mytable VALUES (2005, 5, 0)
INSERT INTO @mytable VALUES (2004, 5, 1)
INSERT INTO @mytable VALUES (2003, 5, 1)
INSERT INTO @mytable VALUES (2002, 5, 1)
SELECT *
FROM @mytable
WHERE date > (SELECT MAX(date) FROM @mytable WHERE [Check] = 0)
