我有以下数据：
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在下一行中发生变化,我不会得到任何后续数据.
将非常感谢帮助
谢谢
埃利
一种方法：
原文链接：https://www.f2er.com/oracle/205233.htmlsql> 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
