sql - Oracle SQL : Select at least the first n rows, 继续直到列值与上一个不同

Question

给定以下结构的表 foo (Oracle 11g):

ID | GROUP_ID
 1 | 100
 2 | 100
 3 | 100
 4 | 200
 5 | 300
 6 | 300
 7 | 400

我想选择前 n 行(按 ID 排序)或更多，这样我总能得到一个完整的组。

例子:

n = 2:我想至少获取前两行，但由于 ID 3 也属于第 100 组，所以我也想获取它。

n = 4:给我前四行，我很高兴 ;-)

n = 5:请求第 1-6 行。

非常感谢您的帮助!

Answer 1

使用 rank() 的解决方案:

select id, group_id
from (select t.*, rank() over (order by group_id) as rnk
    from t)
where rnk <= :n;

构建测试数据:

SQL> create table t (id number not null primary key
  2      , group_id number not null);

Table created.

SQL> insert into t values (1, 100);

1 row created.

SQL> insert into t values (2, 100);

1 row created.

SQL> insert into t values (3, 100);

1 row created.

SQL> insert into t values (4, 200);

1 row created.

SQL> insert into t values (5, 300);

1 row created.

SQL> insert into t values (6, 300);

1 row created.

SQL> insert into t values (7, 400);

1 row created.

SQL> commit;

Commit complete.
SQL>

正在运行...

SQL> var n number
SQL> exec :n := 2;

PL/SQL procedure successfully completed.

SQL> select id, group_id
  2  from (select t.*, rank() over (order by group_id) as rnk
  3      from t)
  4  where rnk <= :n;

        ID   GROUP_ID
---------- ----------
         1        100
         2        100
         3        100

SQL> exec :n := 4;

PL/SQL procedure successfully completed.

SQL> select id, group_id
  2  from (select t.*, rank() over (order by group_id) as rnk
  3      from t)
  4  where rnk <= :n;

        ID   GROUP_ID
---------- ----------
         1        100
         2        100
         3        100
         4        200

SQL> exec :n := 5;

PL/SQL procedure successfully completed.

SQL> select id, group_id
  2  from (select t.*, rank() over (order by group_id) as rnk
  3      from t)
  4  where rnk <= :n;

        ID   GROUP_ID
---------- ----------
         1        100
         2        100
         3        100
         4        200
         5        300
         6        300

6 rows selected.

EDIT 这是包含 for update 子句 (:n = 2) 的版本:

SQL> select id, group_id
  2  from T
  3  where rowid in (select RID
  4      from (select t.rowid as RID, t.*, rank() over (order by group_id) as rnk
  5          from t)
  6      where rnk <= :n)
  7  for update;

        ID   GROUP_ID
---------- ----------
         1        100
         2        100
         3        100