Oracle 实现查主表并取日志表前 N 行行转列

下面SQL的目标是实现在查询主表记录的同时将对应的前三行日志的操作类型、操作时间、操作remark作为9列显示

 --建立测试表,初始化数据
CREATE TABLE TEST_MAIN(
   ID NUMBER,
   NO VARCHAR2(200)
);

CREATE TABLE TEST_MAIN_LOG(
   LOG_ID NUMBER,
   ID NUMBER,
   OPR_TYPE VARCHAR2(2),
   NO VARCHAR2(200),
   OPR_DATE DATE,
   REMARK VARCHAR2(4000)
);


DECLARE I NUMBER;J NUMBER;
BEGIN
  I:=1;
  J:=1;
  LOOP
INSERT INTO TEST_MAIN VALUES(J,TO_CHAR(SYSDATE,'YYYYMMDD')||J);
INSERT INTO TEST_MAIN_LOG VALUES(I,J,0,TO_CHAR(SYSDATE,'YYYYMMDD')||J,SYSDATE-10,'TEST');
I:=I+1;
INSERT INTO TEST_MAIN_LOG VALUES(I,J,1,TO_CHAR(SYSDATE,'YYYYMMDD')||J,SYSDATE-9,'TEST');
I:=I+1;
INSERT INTO TEST_MAIN_LOG VALUES(I,J,2,TO_CHAR(SYSDATE,'YYYYMMDD')||J,SYSDATE-8,'TEST');
I:=I+1;
INSERT INTO TEST_MAIN_LOG VALUES(I,J,3,TO_CHAR(SYSDATE,'YYYYMMDD')||J,SYSDATE-7,'TEST');
I:=I+1;
J:=J+1;
COMMIT;
 EXIT WHEN I>200;
END LOOP;
END;

--查询SQL
SELECT T.ID,
       MAX(T.NO) AS NO,
       MAX(CASE ROW_IDX
             WHEN 1 THEN
              DECODE(LOG_.OPR_TYPE,
                     0,
                     '操作类型1',
                     1,
                     '操作类型2',
                     2,
                     '操作类型3')
             ELSE
              NULL
           END) OPR_TYPE_1,
       MAX(CASE ROW_IDX
             WHEN 1 THEN
              LOG_.OPR_DATE
             ELSE
              NULL
           END) OPR_DATE_1,
       MAX(CASE ROW_IDX
             WHEN 1 THEN
              LOG_.REMARK
             ELSE
              NULL
           END) OPR_REMARK_1,
       MAX(CASE ROW_IDX
             WHEN 2 THEN
              DECODE(LOG_.OPR_TYPE,
                     0,
                     '操作类型1',
                     1,
                     '操作类型2',
                     2,
                     '操作类型3')
             ELSE
              NULL
           END) OPR_TYPE_2,
       MAX(CASE ROW_IDX
             WHEN 2 THEN
              LOG_.OPR_DATE
             ELSE
              NULL
           END) OPR_DATE_2,
       MAX(CASE ROW_IDX
             WHEN 2 THEN
              LOG_.REMARK
             ELSE
              NULL
           END) OPR_REMARK_2,
       MAX(CASE ROW_IDX
             WHEN 3 THEN
              DECODE(LOG_.OPR_TYPE,
                     0,
                     '操作类型1',
                     1,
                     '操作类型2',
                     2,
                     '操作类型3')
             ELSE
              NULL
           END) OPR_TYPE_3,
       MAX(CASE ROW_IDX
             WHEN 3 THEN
              LOG_.OPR_DATE
             ELSE
              NULL
           END) OPR_DATE_3,
       MAX(CASE ROW_IDX
             WHEN 3 THEN
              LOG_.REMARK
             ELSE
              NULL
           END) OPR_REMARK_3
  FROM (SELECT COUNT(LOG.LOG_ID)  -- 利用分析函数算出当前日志行属于主表的记录的第几行日志
               OVER(PARTITION BY LOG.ID 
                    ORDER BY LOG.OPR_DATE 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ROW_IDX,
               LOG.*
          FROM TEST_MAIN_LOG LOG) LOG_,
       TEST_MAIN T
 WHERE T.ID = LOG_.ID(+)
 GROUP BY T.ID
 ORDER BY T.ID