如何在Oracle中查询Data Group by和Order by

Question

我有这样的样本数据

CREATE TABLE table_name (aktif, "START", "END", NO_BOX, QTY) AS
SELECT 1, 'A0001', 'A0020', 2016, 100 FROM DUAL UNION ALL
SELECT 1, 'A0021', 'A0040', 2016, 100 FROM DUAL UNION ALL
SELECT 1, 'A0041', 'A0060', 2016, 100 FROM DUAL UNION ALL
SELECT 0, 'A0061', 'A0080', NULL, 100 FROM DUAL UNION ALL
SELECT 0, 'A0081', 'A0100', NULL, 100 FROM DUAL UNION ALL
SELECT 1, 'A0101', 'A0120', 2016, 100 FROM DUAL UNION ALL
SELECT 1, 'A0121', 'A0140', 2016, 100 FROM DUAL UNION ALL
SELECT 1, 'A0141', 'A0160', 2016, 100 FROM DUAL UNION ALL
SELECT 0, 'A0161', 'A0180', NULL, 100 FROM DUAL UNION ALL
SELECT 0, 'A0181', 'A0200', NULL, 100 FROM DUAL;

我想根据行顺序按AKTIF和NO_BOX保持不变的列分组，然后选择SUM(QTY),MIN(START),MAX(END)。

输出应该是:

<表类>AKTIF开始结束NO_BOX数量tbody><<tr>1A0001A006020163000A0061A0100空2001A0101只有20163000A0161A0200空200

最后，这就是缺口和孤岛问题。

样本数据:

SQL> with test (aktif, cstart, end, no_box, qty) as
2    (select 1, 'A0001', 'A0020', 2016, 100 from dual union all
3     select 1, 'A0021', 'A0040', 2016, 100 from dual union all
4     select 1, 'A0041', 'A0060', 2016, 100 from dual union all
5     --
6     select 0, 'A0061', 'A0080', null, 100 from dual union all
7     select 0, 'A0081', 'A0100', null, 100 from dual union all
8     --
9     select 1, 'A0101', 'A0120', 2016, 100 from dual union all
10     select 1, 'A0121', 'A0140', 2016, 100 from dual union all
11     select 1, 'A0141', 'A0160', 2016, 100 from dual union all
12     --
13     select 0, 'A0161', 'A0180', null, 100 from dual union all
14     select 0, 'A0181', 'A0200', null, 100 from dual
15    ),

查询从这里开始:

16  temp as
17    (select t.*,
18       row_number() over (order by cstart) -
19       row_Number() over (partition by aktif order by cstart) grp
20     from test t
21    )
22  select aktif,
23    min(cstart) cstart,
24    max(end) end,
25    no_box,
26    sum(qty) qty
27  from temp
28  group by aktif, no_box, grp
29  order by cstart;
AKTIF CSTAR END       NO_BOX        QTY
---------- ----- ----- ---------- ----------
1 A0001 A0060       2016        300
0 A0061 A0100                   200
1 A0101 A0160       2016        300
0 A0161 A0200                   200
SQL>