Oracle 读取特定值之前的行

你好，

我在表的单个列中有数据。

创建表t (ctext varchar2(255));

插入t值 ('2017-06-04 17:17-4qxzmh15zwv35-GSAAPP ');
插入到t值 (“公共单元”) 中;
插入t值 ('2017-06-04 17:17-4hcgaf094ysvj-GSAAPP3 ');
插入到t值中 (“公开 _ 小节”);
插入到t值 (“PUBLICATION_SECTIONS”) 中;
插入到t值 ('text_strings ') 中;
插入到t值 (“TRANSLATION_PACKAGES”) 中;
插入t值 ('2017-06-04 17:17-28zsy1aq1zpug-GSAAPP ');
插入到t值 (“PUBLICATION_SECTIONS”) 中;
插入到t值 (“用户”) 中;
插入到t值 ('master_cells') 中;
插入到t值 ('syskey_work ') 中;
插入t值 ('telltale_color_str ');

从上表中，我想要如下所示的输出:

2017-06-04 17:17-4qxzmh15zwv35-GSAAPP，公共单元
2017-06-04 17:17-4hcgaf094ysvj-GSAAPP3，公共 _ 小节 | 文本 _ 字符串 | 翻译 _ 包
2017-06-04 17:17-28zsy1aq1zpug-GSAAPP，宣传 _ 部分 | 用户 | 主 _ 细胞 | 系统 _ 工作 | 电视 _ 颜色 _ str

使用的 “|” 只是分隔值的分隔符。

谢谢，

你假设一个隐含的顺序 (即，插入顺序)，但这并不真正存在，所以我们需要 * 东西 * 来排序行。所以我添加了一个ID列。一旦有了，解决方案就相对简单了

SQL>
SQL> create table t (ctext varchar2(60), id int default seq.nextval);

Table created.

SQL>
SQL> insert into t (ctext) values ('2017-06-04 17:17-4qxzmh15zwv35-GSAAPP');

1 row created.

SQL> insert into t (ctext) values (' PUBLICATION_CELLS ');

1 row created.

SQL> insert into t (ctext) values ('2017-06-04 17:17-4hcgaf094ysvj-GSAAPP3');

1 row created.

SQL> insert into t (ctext) values (' PUBLICATION_SUBSECTIONS ');

1 row created.

SQL> insert into t (ctext) values (' PUBLICATION_SECTIONS ');

1 row created.

SQL> insert into t (ctext) values (' TEXT_STRINGS ');

1 row created.

SQL> insert into t (ctext) values (' TRANSLATION_PACKAGES ');

1 row created.

SQL> insert into t (ctext) values ('2017-06-04 17:17-28zsy1aq1zpug-GSAAPP');

1 row created.

SQL> insert into t (ctext) values (' PUBLICATION_SECTIONS ');

1 row created.

SQL> insert into t (ctext) values (' USERS ');

1 row created.

SQL> insert into t (ctext) values (' MASTER_CELLS ');

1 row created.

SQL> insert into t (ctext) values (' SYSKEY_WORK ');

1 row created.

SQL> insert into t (ctext) values (' TELLTALE_COLOR_STR ');

1 row created.

SQL> select * from t;

CTEXT                                                                ID
------------------------------------------------------------ ----------
2017-06-04 17:17-4qxzmh15zwv35-GSAAPP                                 1
 PUBLICATION_CELLS                                                    2
2017-06-04 17:17-4hcgaf094ysvj-GSAAPP3                                3
 PUBLICATION_SUBSECTIONS                                              4
 PUBLICATION_SECTIONS                                                 5
 TEXT_STRINGS                                                         6
 TRANSLATION_PACKAGES                                                 7
2017-06-04 17:17-28zsy1aq1zpug-GSAAPP                                 8
 PUBLICATION_SECTIONS                                                 9
 USERS                                                               10
 MASTER_CELLS                                                        11
 SYSKEY_WORK                                                         12
 TELLTALE_COLOR_STR                                                  13

13 rows selected.

SQL>
SQL>
SQL> select listagg(ctext,'|') within group ( order by x, id )
  2  from (
  3  select last_value(rn ignore nulls) over ( order by id) x,
  4         id, ctext
  5  from (
  6  select
  7    case when ctext like '20%' then row_number() over ( order by id ) end as rn,
  8    id,
  9    ctext
 10  from t
 11  )
 12  )
 13  group by x
 14  order by x
 15  ;

LISTAGG(CTEXT,'|')WITHINGROUP(ORDERBYX,ID)
----------------------------------------------------------------------------------------------------------------------------------
2017-06-04 17:17-4qxzmh15zwv35-GSAAPP| PUBLICATION_CELLS
2017-06-04 17:17-4hcgaf094ysvj-GSAAPP3| PUBLICATION_SUBSECTIONS | PUBLICATION_SECTIONS | TEXT_STRINGS | TRANSLATION_PACKAGES
2017-06-04 17:17-28zsy1aq1zpug-GSAAPP| PUBLICATION_SECTIONS | USERS | MASTER_CELLS | SYSKEY_WORK | TELLTALE_COLOR_STR

3 rows selected.

SQL>
复制