oracle 从父表中获取前X行，而不考虑通过联接一个到多个子表而产生的行

我有两个具有一对多关系的数据库表PERSON_TABLE和FILE_TABLE。我需要能够将结果限制为给予前X行的人数。同时能够在FILE_TABLE中的列上进行 join 和过滤。我需要获取前两个person，而不管文件表上的连接生成多少行。

PERSON_TABLE

| ID|名称|
| --|--|
| 1 |埃里克|
| 2 |约翰|

FILE_TABLE

| ID|个人ID|文件名称|
| --|--|--|
| 1 | 1 | 1.xml |
| 2 | 1 | 2.xml |
| 3 | 2 | 3.xml |

尝试-加入并获取下一个

select *
from (select *
      from person_table person,
           file_table file
      where person.id = file.person_id)
offset 0 rows fetch next 2 rows only;

结果：这不起作用，因为Eric有两个文件，所以这将只返回包含Eric的行。

解决方法尝试-listag

通过使用listagg，我可以将所需的FILE_TABLE行 concatinate 到PERSON_TABLE行中，但我不认为它看起来很优雅。
有没有其他明显的方法来做到这一点，我错过了？

使用子查询筛选人员，然后加入。如果您想要每个人都至少有一个文件的行：

SELECT *
FROM   ( SELECT *
         FROM   person_table p
         WHERE  EXISTS(SELECT 1 FROM file_table f WHERE p.id = f.person_id)
         FETCH FIRST 2 ROWS ONLY ) p
       INNER JOIN file_table f
       ON  p.id = f.person_id;

或者，使用解析函数：

SELECT id,
       name,
       file_name,
       person_id
FROM   (
  SELECT p.*,
         f.*,
         DENSE_RANK() OVER (ORDER BY p.name, p.id) AS rnk
  FROM   person_table p
         INNER JOIN file_table f
         ON  p.id = f.person_id
)
WHERE  rnk <= 2

其中，对于样本数据：

CREATE TABLE person_table (id, name) AS
SELECT 1, 'Alice' FROM DUAL UNION ALL
SELECT 2, 'Betty' FROM DUAL UNION ALL
SELECT 3, 'Carol' FROM DUAL UNION ALL
SELECT 4, 'Debra' FROM DUAL;

CREATE TABLE file_table (file_name, person_id) AS
SELECT 'A', 1 FROM DUAL UNION ALL
SELECT 'B', 1 FROM DUAL UNION ALL
SELECT 'C', 1 FROM DUAL UNION ALL
SELECT 'D', 3 FROM DUAL;

两者都输出前两个人的文件：
| ID|名称|文件名称|个人ID|
| --|--|--|--|
| 1 |爱丽丝|一| 1 |
| 1 |爱丽丝|B| 1 |
| 1 |爱丽丝|C| 1 |
| 3 |卡罗尔|D| 3 |
如果你还想过滤每个人的文件数量，那么使用LATERAL连接并在横向连接内过滤：

SELECT *
FROM   ( SELECT *
         FROM   person_table p
         WHERE  EXISTS(SELECT 1 FROM file_table f WHERE p.id = f.person_id)
         FETCH FIRST 2 ROWS ONLY ) p
       CROSS JOIN LATERAL (
         SELECT *
         FROM   file_table f
         WHERE  p.id = f.person_id
         FETCH FIRST 2 ROWS ONLY
       );

或者，再用解析函数：

SELECT id,
       name,
       file_name,
       person_id
FROM   (
  SELECT p.*,
         f.*,
         DENSE_RANK() OVER (ORDER BY p.name, p.id) AS rnk,
         ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY f.file_name) AS rn
  FROM   person_table p
         INNER JOIN file_table f
         ON  p.id = f.person_id
)
WHERE  rnk <= 2
AND    rn <= 2;

两者输出：
| ID|名称|文件名称|个人ID|
| --|--|--|--|
| 1 |爱丽丝|一| 1 |
| 1 |爱丽丝|B| 1 |
| 3 |卡罗尔|D| 3 |
如果你想得到前两个人，不管他们有零个还是多个文件，那么使用OUTER JOIN：

SELECT *
FROM   ( SELECT *
         FROM   person_table
         FETCH FIRST 2 ROWS ONLY ) p
       LEFT OUTER JOIN file_table f
       ON p.id = f.person_id;

或：

SELECT id,
       name,
       file_name,
       person_id
FROM   (
  SELECT p.*,
         f.*,
         DENSE_RANK() OVER (ORDER BY p.name, p.id) AS rnk
  FROM   person_table p
         LEFT OUTER JOIN file_table f
         ON  p.id = f.person_id
)
WHERE  rnk <= 2

其输出：
| ID|名称|文件名称|个人ID|
| --|--|--|--|
| 1 |爱丽丝|一| 1 |
| 1 |爱丽丝|B| 1 |
| 1 |爱丽丝|C| 1 |
| 2 |贝蒂| * 空 | 空 *|
而且，如果你想过滤每个人的文件，那么再一次使用LATERAL连接：

SELECT *
FROM   ( SELECT *
         FROM   person_table
         FETCH FIRST 2 ROWS ONLY ) p
       LEFT OUTER JOIN LATERAL (
         SELECT *
         FROM   file_table f
         WHERE  p.id = f.person_id
         FETCH FIRST 2 ROWS ONLY
       )
       ON (1 = 1);

或：

SELECT id,
       name,
       file_name,
       person_id
FROM   (
  SELECT p.*,
         f.*,
         DENSE_RANK() OVER (ORDER BY p.name, p.id) AS rnk,
         ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY f.file_name) AS rn
  FROM   person_table p
         LEFT OUTER JOIN file_table f
         ON  p.id = f.person_id
)
WHERE  rnk <= 2
AND    rn <= 2

| ID|名称|文件名称|个人ID|
| --|--|--|--|
| 1 |爱丽丝|一| 1 |
| 1 |爱丽丝|B| 1 |
| 2 |贝蒂| * 空 | 空 *|
fiddle

oracle 从父表中获取前X行，而不考虑通过联接一个到多个子表而产生的行

1条答案

相关问题

热门标签

最新问答