当前位置： 文江博客话题详情

Oracle SQL：至少选择前n行，继续直到列值与最后一行不同

发布于 2024-11-26 09:07:26 字数 320 浏览 1 评论 0原文

给定以下结构的表 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 行。

非常感谢您的帮助！

原文

given a table foo of the following structure (Oracle 11g):

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

I want to select the first n rows (ordered by ID) or more, such that I always get a complete group.

Example:

n = 2: I want to get at least the first two rows, but since ID 3 also belongs to group 100, I want to get that as well.

n = 4: Give me the first four rows and I am happy ;-)

n = 5: Rows 1-6 are requested.

Your help is highly appreciated!

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（4）

魄砕の薆 2024-12-03 09:07:26

使用 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.

编辑 这是包含 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

Solution using rank():

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

Building test data:

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>

Running...

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 Here is version that includes the for update clause (: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
回复 原文
辞旧 2024-12-03 09:07:26

如果 GROUP_ID 始终是连续且升序的，那么可以通过使用分析 ROW_NUMBER() 函数的 SQL 轻松解决此问题：

SQL> select id
  2         , group_id
  3  from foo
  4  where group_id <= ( select group_id
  5                     from (
  6                              select f.group_id
  7                                     , row_number() over (order by f.id asc) rn
  8                              from foo f
  9                              )
 10                          where rn = &n )
 11  order by id
 12  /
Enter value for n: 2
old  10:                         where rn = &n )
new  10:                         where rn = 2 )

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

SQL> r
  1  select id
  2         , group_id
  3  from foo
  4  where group_id <= ( select group_id
  5                     from (
  6                              select f.group_id
  7                                     , row_number() over (order by f.id asc) rn
  8                              from foo f
  9                              )
 10                          where rn = &n )
 11* order by id
Enter value for n: 4
old  10:                         where rn = &n )
new  10:                         where rn = 4 )

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

SQL> r
  1  select id
  2         , group_id
  3  from foo
  4  where group_id <= ( select group_id
  5                     from (
  6                              select f.group_id
  7                                     , row_number() over (order by f.id asc) rn
  8                              from foo f
  9                              )
 10                          where rn = &n )
 11* order by id
Enter value for n: 5
old  10:                         where rn = &n )
new  10:                         where rn = 5 )

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

6 rows selected.

SQL>

If it is always true that GROUP_ID is contiguous and ascending, then this is easily solved with SQL using an analytical ROW_NUMBER() function:

SQL> select id
  2         , group_id
  3  from foo
  4  where group_id <= ( select group_id
  5                     from (
  6                              select f.group_id
  7                                     , row_number() over (order by f.id asc) rn
  8                              from foo f
  9                              )
 10                          where rn = &n )
 11  order by id
 12  /
Enter value for n: 2
old  10:                         where rn = &n )
new  10:                         where rn = 2 )

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

SQL> r
  1  select id
  2         , group_id
  3  from foo
  4  where group_id <= ( select group_id
  5                     from (
  6                              select f.group_id
  7                                     , row_number() over (order by f.id asc) rn
  8                              from foo f
  9                              )
 10                          where rn = &n )
 11* order by id
Enter value for n: 4
old  10:                         where rn = &n )
new  10:                         where rn = 4 )

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

SQL> r
  1  select id
  2         , group_id
  3  from foo
  4  where group_id <= ( select group_id
  5                     from (
  6                              select f.group_id
  7                                     , row_number() over (order by f.id asc) rn
  8                              from foo f
  9                              )
 10                          where rn = &n )
 11* order by id
Enter value for n: 5
old  10:                         where rn = &n )
new  10:                         where rn = 5 )

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

6 rows selected.

SQL>
回复 原文
何以畏孤独 2024-12-03 09:07:26

如果您的 ID 始终从 1 开始连续（无间隙）。并且如果您的 Group_ID 从未在其他地方作为第二组出现。如果您的 Group_ID 值始终在上升...

SELECT
  *
FROM
  foo
WHERE
  Group_ID <= (SELECT Group_ID FROM foo WHERE ID = n)
ORDER BY
  ID

您将受益于在 ID 和 Group_ID 上建立单独的索引

If your IDs are always sequential (without gaps) from 1. And if your Group_IDs never occur as a second group elsewhere. And if your Group_IDs are always ascending in value...

SELECT
  *
FROM
  foo
WHERE
  Group_ID <= (SELECT Group_ID FROM foo WHERE ID = n)
ORDER BY
  ID

You'll benefit here from having separate indexes on ID and Group_ID

回复 原文
魔法少女 2024-12-03 09:07:26

如果我们假设 group_id 是连续且升序的，那么@Shannon 的答案就完美。如果我们不做这个假设，并且我们有如下所示的数据，例如：

SQL> select * from foo order by id;

ID GROUP_ID
-- --------
 1      100
 2      100
 3      100
 4      200
 6      100
 7      400
 9      500
10      500
11      500
12      600

那么这是一个更棘手的问题。例如，如果N = 3、4或5，那么我们需要通过ID = 6获取行。对于N = 6，我们需要最多ID = 7。对于N = 7，我们需要通过ID = 11。

我相信无论 group_id 的顺序如何，此查询都有效：

For N = 7:

WITH q AS (SELECT ID, group_id
                , row_number() OVER (ORDER BY ID) rn
                , MAX(id) OVER (PARTITION BY group_id) rn2
             FROM foo)
SELECT ID, group_id FROM q
 WHERE ID <= (SELECT max(rn2) FROM q WHERE rn <= :N)
 ORDER BY ID; 

ID GROUP_ID
-- --------
 1      100
 2      100
 3      100
 4      200
 6      100
 7      400
 9      500
10      500
11      500

9 rows selected

For N = 6:

ID GROUP_ID
-- --------
 1      100
 2      100
 3      100
 4      200
 6      100
 7      400

For N = 1:

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

If we assume that the group_id's are contiguous and ascending, then @Shannon's answer works perfectly. If we do not make that assumption, and we have data that looks like this, for example:

SQL> select * from foo order by id;

ID GROUP_ID
-- --------
 1      100
 2      100
 3      100
 4      200
 6      100
 7      400
 9      500
10      500
11      500
12      600

Then it's a stickier problem. For example, if N = 3, 4, or 5, then we need to get the rows through ID = 6. For N = 6, we need up to ID = 7. For N = 7, we need through ID = 11.

I believe this query works regardless of the order of group_id:

For N = 7:

WITH q AS (SELECT ID, group_id
                , row_number() OVER (ORDER BY ID) rn
                , MAX(id) OVER (PARTITION BY group_id) rn2
             FROM foo)
SELECT ID, group_id FROM q
 WHERE ID <= (SELECT max(rn2) FROM q WHERE rn <= :N)
 ORDER BY ID; 

ID GROUP_ID
-- --------
 1      100
 2      100
 3      100
 4      200
 6      100
 7      400
 9      500
10      500
11      500

9 rows selected

For N = 6:

ID GROUP_ID
-- --------
 1      100
 2      100
 3      100
 4      200
 6      100
 7      400

For N = 1:

ID GROUP_ID
-- --------
 1      100
 2      100
 3      100
 4      200
 6      100
回复 原文
~没有更多了~

关于作者

缪败

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

離殇

文章 0 评论 0

小姐丶请自重

文章 0 评论 0

Aik

文章 0 评论 0

国产ˉ祖宗

文章 0 评论 0

猥琐帝

文章 0 评论 0

半仙

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文