sql - 数据定义的oracle解析函数窗口

Question

我有一个表格，它代表从特定文本文件格式读取的数据的逐行转储。每条线可能代表一个“主”或“细节”线，通过 rec_type 指示。代码。我想编写一个查询，在关联的详细信息行旁边获取“主”行。我想出了一些可以完成这项工作的东西，但它似乎有点hackish，并且对更好的方法感兴趣(如果有的话)。

CREATE TABLE mdtest
 (rec_seq  NUMBER        PRIMARY KEY
 ,rec_type VARCHAR2(3)   NOT NULL
 ,rec_data VARCHAR2(100) NOT NULL);

INSERT INTO mdtest VALUES (1, '100', 'Bill Jones');
INSERT INTO mdtest VALUES (2, '200', '20080115,100.25');
INSERT INTO mdtest VALUES (3, '100', 'John Smith');
INSERT INTO mdtest VALUES (4, '200', '20090701,80.95');
INSERT INTO mdtest VALUES (5, '200', '20091231,110.35');

预期结果:

SEQ_EMP  EMP_NAME    SEQ_DATA  EMP_DATA
=======  ==========  ========  ===============
      1  Bill Jones         2  20080115,100.25
      3  John Smith         4  20090701,80.95
      3  John Smith         5  20091231,110.35

假设:

记录按 rec_seq

的顺序处理

第一个记录类型是“100”

每个“100 ”记录在

之后都有 1 个或多个“ 200 ”记录

注意:这是针对 Oracle 9i 的，但是我们今年应该升级到 11g R1。

Answer 1

这是我到目前为止所拥有的:

SELECT seq_emp 
      ,SUBSTR(emp_seq_name,10) emp_name 
      ,seq_data 
      ,emp_data 
FROM  (SELECT MAX(CASE WHEN rec_type = '100' THEN rec_seq END) 
              OVER (ORDER BY rec_seq 
                    ROWS BETWEEN UNBOUNDED PRECEDING 
                             AND CURRENT ROW) seq_emp 
             ,MAX(CASE 
                  WHEN rec_type = '100' 
                  THEN TO_CHAR(rec_seq,'fm00000000') || '|' || rec_data 
                  END) 
              OVER (ORDER BY rec_seq 
                    ROWS BETWEEN UNBOUNDED PRECEDING 
                             AND CURRENT ROW) emp_seq_name 
             ,rec_seq seq_data 
             ,rec_type 
             ,rec_data emp_data 
       FROM   mdtest) 
WHERE  rec_type = '200' 
ORDER BY seq_data; 

如您所见，我正在使用 MAX 报告分析函数，该函数带有一个从集合顶部开始到当前行的窗口，以获取当前“200”记录的相关“100”记录；然后在外部查询中，我丢弃了不需要的“100”条记录。

要获得 emp_name，我必须将数据附加到 rec_seq 中，以便 MAX 函数仍然选择正确的头记录；然后在外部查询中，我将 rec_seq 砍掉。

我使用过其他分析函数和语法，包括 FIRST_VALUE 和 KEEP 语法，但这些似乎都没有使这项工作更简单；困难在于窗口是由 rec_type 的值定义的，而不是一些常量偏移量。