我需要復制每個組中的排序行 - “n_group”，以便輸出為每組中的 5 行。該組應填充該組前幾行的值。
輸入表：
| n_組 | n_sort | n_name |
|---|---|---|
| 啊 | 1 | pr_1 |
| bb | 1 | pr_1 |
| bb | 2 | pr_2 |
| 抄送 | 1 | pr_4 |
| 抄送 | 2 | pr_8 |
| 抄送 | 3 | pr_9 |
期望的結果：
| n_組 | n_sort | n_name |
|---|---|---|
| 啊 | 1 | pr_1 |
| 啊 | 1 | pr_1 |
| 啊 | 1 | pr_1 |
| 啊 | 1 | pr_1 |
| 啊 | 1 | pr_1 |
| bb | 1 | pr_1 |
| bb | 2 | pr_2 |
| bb | 1 | pr_1 |
| bb | 2 | pr_2 |
| bb | 1 | pr_1 |
| 抄送 | 1 | pr_4 |
| 抄送 | 2 | pr_8 |
| 抄送 | 3 | pr_9 |
| 抄送 | 1 | pr_4 |
| 抄送 | 2 | pr_8 |
uj5u.com熱心網友回復：
這是一個選項 - 它使用分層查詢。看看有沒有幫助。
樣本資料：
SQL> with test (n_group, n_sort, n_name) as
2 (select 'aa', 1, 'pr_1' from dual union all
3 select 'bb', 1, 'pr_1' from dual union all
4 select 'bb', 2, 'pr_2' from dual union all
5 select 'cc', 1, 'pr_4' from dual union all
6 select 'cc', 2, 'pr_8' from dual union all
7 select 'cc', 3, 'pr_9' from dual
8 ),
查詢從這里開始；將現有行乘以第 15 行回傳的次數；row_number分析函式的結果稍后用于獲取每個 each 的前 5 行n_group。
9 temp as
10 (select t.n_group, t.n_sort, t.n_name,
11 row_number() over (partition by n_group order by n_sort, n_name) rn
12 from test t cross join
13 table(cast(multiset(select level from dual
14 connect by level <=
15 (select 5 - case when max(b.n_sort) = 1 then 0 else max(b.n_sort) end
16 from test b
17 where b.n_group = t.n_group)
18 ) as sys.odcinumberlist))
19 )
20 select n_group, n_sort, n_name
21 from temp
22 where rn <= 5
23 order by n_group, n_sort, n_name;
結果：
N_GROUP N_SORT N_NAME
---------- ---------- ----------
aa 1 pr_1
aa 1 pr_1
aa 1 pr_1
aa 1 pr_1
aa 1 pr_1
bb 1 pr_1
bb 1 pr_1
bb 1 pr_1
bb 2 pr_2
bb 2 pr_2
cc 1 pr_4
cc 1 pr_4
cc 2 pr_8
cc 2 pr_8
cc 3 pr_9
15 rows selected.
uj5u.com熱心網友回復：
您可以使用PARTITIONed 連接到行生成器函式：
SELECT n_group, n_sort, n_name
FROM ( SELECT LEVEL AS n FROM DUAL CONNECT BY LEVEL <= 5 ) l
INNER JOIN (
SELECT t.*,
COUNT(*) OVER (PARTITION BY n_group) AS n_rows
FROM table_name t
) t
PARTITION BY (t.n_group)
ON (MOD(l.n - 1, t.n_rows) 1 = t.n_sort)
ORDER BY t.n_group, l.n;
其中，對于樣本資料：
CREATE TABLE table_name (n_group, n_sort, n_name) AS
SELECT 'aa', 1, 'pr_1' FROM DUAL UNION ALL
SELECT 'bb', 1, 'pr_1' FROM DUAL UNION ALL
SELECT 'bb', 2, 'pr_2' FROM DUAL UNION ALL
SELECT 'cc', 1, 'pr_4' FROM DUAL UNION ALL
SELECT 'cc', 2, 'pr_8' FROM DUAL UNION ALL
SELECT 'cc', 3, 'pr_9' FROM DUAL;
輸出：
N_GROUP N_SORT N_NAME 啊 1 pr_1 啊 1 pr_1 啊 1 pr_1 啊 1 pr_1 啊 1 pr_1 bb 1 pr_1 bb 2 pr_2 bb 1 pr_1 bb 2 pr_2 bb 1 pr_1 抄送 1 pr_4 抄送 2 pr_8 抄送 3 pr_9 抄送 1 pr_4 抄送 2 pr_8
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/483093.html
上一篇：年月日總和-OracleSQL
下一篇：OracleDBRMAN備份