oracle选择每一组的第二条,如何使用Oracle SQL选择第一组连续行

一种方法：

SQL> WITH DATA AS (

2 SELECT '1/01/2000' mydate, 1 GroupID, 44 Value FROM DUAL

3 UNION ALL SELECT '2/01/2000', 1, 55 FROM DUAL

4 UNION ALL SELECT '3/01/2000', 1, 66 FROM DUAL

5 UNION ALL SELECT '4/01/2000', 2, 77 FROM DUAL

6 UNION ALL SELECT '5/01/2000', 2, 88 FROM DUAL

7 UNION ALL SELECT '6/01/2000', 1, 99 FROM DUAL

8 UNION ALL SELECT '7/01/2000', 1, 22 FROM DUAL

9 )

10 SELECT mydate, groupid, VALUE

11 FROM (SELECT mydate, groupid, VALUE,

12 SUM(gap) over(ORDER BY mydate) contiguous_group

13 FROM (SELECT mydate, groupid, VALUE,

14 CASE

15 WHEN lag(groupid)

16 over(ORDER BY mydate) != groupid

17 THEN

18 1

19 ELSE

20 0

21 END gap

22 FROM DATA))

23 WHERE contiguous_group = 0;

MYDATE GROUPID VALUE

--------- ---------- ----------

1/01/2000 1 44

2/01/2000 1 55

3/01/2000 1 66