针对 Oracle 数据库的控制中断类型 SQL。

Question

我正在与一个设计奇怪（我们称之为次优）的Oracle数据库一起工作。其中一个问题是，其中一个列包含两种不同类型的文本数据：“标题”数据和“内容”数据。就像这样：

id	text
1	Header 1:
2	abc
3	def
4	Header 2:
5	ghi
6	jkl
7	mno
8	Header 3:
9	pqr

如果可能的话，我需要构建SQL语句，以便将“Header”行作为“内容”行的控制中断返回，以获得这些结果：

Header 1: abc
Header 1: def
Header 2: ghi
Header 2: jkl
Header 2: mno
Header 3: pqr

id值是顺序的，但是每个标题的内容可以跨越任意数量的行。因此，确定每个“内容”行所属的“标题”的唯一线索是先前看到的标题（文本类似于'%Header%'且id值最大，小于当前行的id值）。

- Woodchuck

---

最简单的方法是编写一个PL/SQL过程。你能使用PL/SQL吗？或者必须使用SQL？ - Simon Martinelli

Answer 1

另一种方法：

WITH cteTestdata(ID, TEXT) AS
       (SELECT 1,   'Header 1:' FROM DUAL UNION ALL
        SELECT 2,   'abc' FROM DUAL UNION ALL
        SELECT 3,   'def' FROM DUAL UNION ALL
        SELECT 4,   'Header 2:' FROM DUAL UNION ALL
        SELECT 5,   'ghi' FROM DUAL UNION ALL
        SELECT 6,   'jkl' FROM DUAL UNION ALL
        SELECT 7,   'mno' FROM DUAL UNION ALL
        SELECT 8,   'Header 3:' FROM DUAL UNION ALL
        SELECT 9,   'pqr' FROM DUAL),
     cteHeaders AS
       (SELECT ID, TEXT
          FROM cteTestdata
          WHERE TEXT LIKE 'Header%'
          ORDER BY ID),
     cteLines AS
       (SELECT ID, TEXT
          FROM cteTestdata
          WHERE TEXT NOT LIKE 'Header%'
          ORDER BY ID)
SELECT h.TEXT || ' ' || l.TEXT AS COMBINED_TEXT
  FROM cteLines l
  INNER JOIN cteHeaders h
    ON h.ID = (SELECT MAX(h2.ID)
                 FROM cteHeaders h2
                 WHERE h2.ID < l.ID)
  ORDER BY l.ID

这里有一个db<>fiddle示例

- Bob Jarvis - Слава Україні

Answer 2

你可以尝试使用类似以下代码的方式：

select header || text
from 
(
    select
        max (case when text like 'Header%' then text end) over (partition by 1 order by id) as header,
        id, text
    from yourTable
)
where text not like 'Header%'    

- Aleksej

---

这不是一个解决方案，一旦标题无序（比如说标题3、标题2、标题1），它就会出错。 - Petr

Answer 3

我使用 match_recognize 解决这个问题，它非常适合分析行序列。

with t(id, text) as (
  select 1, 'Header 3:' from dual union all
  select 2, 'abc'       from dual union all
  select 3, 'def'       from dual union all
  select 4, 'Header 1:' from dual union all
  select 5, 'ghi'       from dual union all
  select 6, 'jkl'       from dual union all
  select 7, 'mno'       from dual union all
  select 8, 'Header 2:' from dual union all
  select 9, 'pqr'       from dual 
)
select id, header, text
from t match_recognize (
  order by id
  measures
    header.text header
  all rows per match
  pattern ({- header -} data*)
  define
    data as text not like 'Header%'
);

我惊讶的发现使用match_recognize经常能够得出简单的解决方案。

- Petr

---

很好。一个小的改进就是将header包装在{- ... -}中（这意味着“从输出中排除该行”），而不是在where子句中;两者都可以实现相同的结果，但如果将其放在模式pattern子句中，则match_recognize处理会少做一点工作。 (然后您将不需要测量 classifier()，因为您将不再需要它。) 无论如何，这也是我最喜欢的解决方案。 - user5683823

@mathguy 谢谢你指出这一点，我以为这是可能的，但是找不到它。回答已更新。 - Petr

这太棒了。不幸的是，看起来match_recognize可能是在Oracle 12C中引入的，而我只有11G。唉。 - Woodchuck

@Gordon Linoff有最佳答案。 - Petr

Answer 4

这是对Aleksej答案的一个变化版本，可以使用：

select header || ' ' || text
from (select t.*,
             lag(case when text like 'Header%' then text end ignore nulls) over (order by id) as header 
      from t
     ) t
where text not like 'Header%';

- Gordon Linoff

---

非常好用！而且对我来说有一个优点，就是可以与Oracle 11G一起使用。 - Woodchuck