Oracle遍历列上的值 [英] Oracle iterates thru values on columns

问题描述

所以我有一些像这样的数据

So I have some data like this

NO| ID    | PID | COUNT
1 | 00033 | P4  | 1
2 | 00033 | P3  | 3
3 | 00033 | P2  | 2

我想像这样基于计数值迭代ID和PID

i want to iterate the ID and PID based on count values, like this

NO| ID    | PID
1 | 00033 | P4
2 | 00033 | P3
3 | 00033 | P3
4 | 00033 | P3
5 | 00033 | P2
6 | 00033 | P2

如何使用Oracle PL/SQL过程/游标实现此目标的最佳方法.

hows the best way to achieve this using Oracle PL/SQL procedure / cursor.

关于， 瑞安

推荐答案

使用普通SQL:

SELECT row_number() OVER ( ORDER BY t."ID", t."PID" DESC ) as NO, 
       t."ID", t."PID"
FROM Table1 t
CROSS APPLY(
  SELECT 1 FROM dual
  CONNECT BY level <= t."COUNT"
)
ORDER BY t."ID", t."PID" DESC

演示: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=d0840879efd244

这篇关于Oracle遍历列上的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！