如何使用Oracle SQL选择第一组连续行

Date             GroupID     Value 
1/01/2000    1                 44 
2/01/2000    1                 55 
3/01/2000    1                 66 
4/01/2000    2                 77 
5/01/2000    2                 88 
6/01/2000    1                 99 
7/01/2000    1                 22 

我正在寻找能够在按日期订购时选择具有连续GroupID的第一组记录的查询.即在这个例子中我会得到：

1/01/2000    1                 44 
2/01/2000    1                 55 
3/01/2000    1                 66 

随着组ID在下一行中发生变化,我不会得到任何后续数据.

将非常感谢帮助

谢谢

埃利

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',66 FROM DUAL
  5  UNION ALL SELECT '4/01/2000',2,77 FROM DUAL
  6  UNION ALL SELECT '5/01/2000',88 FROM DUAL
  7  UNION ALL SELECT '6/01/2000',99 FROM DUAL
  8  UNION ALL SELECT '7/01/2000',22 FROM DUAL
  9  )
 10  SELECT mydate,groupid,VALUE
 11    FROM (SELECT mydate,VALUE,12                 SUM(gap) over(ORDER BY mydate) contiguous_group
 13             FROM (SELECT mydate,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!