创建逗号分隔记录的Oracle SQL 10g查询

Question

我正在编写一个用于创建jasper报告的oracle 10g查询。

这是一个问题-

SELECT     essay_id,
           LTRIM (
              MAX (SYS_CONNECT_BY_PATH (full_name, ','))
                 KEEP (DENSE_RANK LAST ORDER BY curr),
              ',')
              AS full_name
FROM       (SELECT essay_id,
                   full_name,
                   ROW_NUMBER () OVER (PARTITION BY essay_id ORDER BY full_name)
                      AS curr,
                     ROW_NUMBER ()
                        OVER (PARTITION BY essay_id ORDER BY full_name)
                   - 1
                      AS prev
            FROM   (SELECT a.id AS essay_id,
                           CASE NVL (firstname, 'NULL FIRSTNAME')
                              WHEN 'NULL FIRSTNAME' THEN username
                              ELSE (firstname || ' ' || lastname)
                           END
                              AS full_name
                    FROM   essay_table a
                           INNER JOIN essay_writer_join ej ON a.id = ej.essay_id
                           INNER JOIN writer_table u ON ej.user_id = u.id))
GROUP BY   essay_id
CONNECT BY prev = PRIOR curr AND essay_id = PRIOR essay_id
START WITH curr = 1

文章是独特的，但可以有多个作家(essay_writer_join)，这个查询给我的文章与作家，是用逗号隔开。

问题是，我需要再添加一个名为" manager“的列，它将显示作者的经理。经理信息在WRITER_TABLE中，列名为"manager_name“。essay_table有作者的名字、姓氏和用户名。棘手之处在于，两个作家可以有两个不同的经理。例如，在论文“123”中，作者是'abc‘和'xyz’，这些作者的管理者分别是'lmo‘和'pqr’，那么记录应该以以下格式显示

essay id      writer       manager
123          abc, xyz      lmo, pqr

这在oracle 10g sql中是可能的吗？我试图寻找类似的情况，但没有找到任何相关的解决方案。

Answer 1

您只需将manager_name包含在最内部的查询和中间的查询中，然后也可以重复该列的聚合机制。我已经更改了列和表别名，使之更加一致(我还简化了您的案例表达式)：

SELECT     essay_id,
           LTRIM (
              MAX (SYS_CONNECT_BY_PATH (writer, ','))
                 KEEP (DENSE_RANK LAST ORDER BY curr),
              ',')
              AS writer,
           LTRIM (
              MAX (SYS_CONNECT_BY_PATH (manager, ','))
                 KEEP (DENSE_RANK LAST ORDER BY curr),
              ',')
              AS manager
FROM       (SELECT essay_id,
                   writer,
                   manager,
                   ROW_NUMBER () OVER (PARTITION BY essay_id ORDER BY writer)
                      AS curr,
                     ROW_NUMBER ()
                        OVER (PARTITION BY essay_id ORDER BY writer)
                   - 1
                      AS prev
            FROM   (SELECT et.id AS essay_id,
                           CASE WHEN wt.firstname IS NULL THEN wt.username
                              ELSE (wt.firstname || ' ' || wt.lastname)
                           END
                              AS writer,
                           wt.manager_name as manager
                    FROM   essay_table et
                           INNER JOIN essay_writer_join ej ON et.id = ej.essay_id
                           INNER JOIN writer_table wt ON ej.user_id = wt.id))
GROUP BY   essay_id
CONNECT BY prev = PRIOR curr AND essay_id = PRIOR essay_id
START WITH curr = 1;

  ESSAY_ID WRITER     MANAGER  
---------- ---------- ----------
       123 abc,xyz    lmo,pqr   

我能看到的唯一真正的问题是，如果一篇文章的两位作者都有相同的管理者，那么你会看到这个价值被重复了：

  ESSAY_ID WRITER     MANAGER  
---------- ---------- ----------
       123 abc,xyz    lmo,pqr   
       456 abc,def    lmo,lmo   

这也许是你想要看到的。

演示。

如果您确实希望抑制重复的管理器，则可以将聚合分离为子查询。我发现，将这些内容放入常见的表表达式(a.k.a )中更容易阅读。(子查询重构)：

WITH all_data AS (
  SELECT et.id AS essay_id,
         CASE WHEN wt.firstname IS NULL THEN wt.username
            ELSE (wt.firstname || ' ' || wt.lastname)
         END
            AS writer,
         wt.manager_name as manager
  FROM   essay_table et
  JOIN   essay_writer_join ej ON et.id = ej.essay_id
  JOIN   writer_table wt ON ej.user_id = wt.id
),
writers AS (
  SELECT essay_id,
         writer,
         ROW_NUMBER () OVER (PARTITION BY essay_id ORDER BY writer) AS rn
  FROM   (
    SELECT distinct essay_id, writer
    FROM   all_data
  )
),
managers AS (
  SELECT essay_id,
         manager,
         ROW_NUMBER () OVER (PARTITION BY essay_id ORDER BY manager) AS rn
  FROM   (
    SELECT distinct essay_id, manager
    FROM   all_data
  )
)
SELECT   DISTINCT ad.essay_id,
         (
           SELECT     LTRIM (MAX (SYS_CONNECT_BY_PATH (w.writer, ','))
                        KEEP (DENSE_RANK LAST ORDER BY w.rn), ',')
           FROM       writers w
           WHERE      w.essay_id = ad.essay_id
           CONNECT BY w.rn = PRIOR w.rn + 1 AND w.essay_id = PRIOR w.essay_id
           START WITH w.rn = 1
         ) AS writer,
         (
           SELECT     LTRIM (MAX (SYS_CONNECT_BY_PATH (m.manager, ','))
                        KEEP (DENSE_RANK LAST ORDER BY m.rn), ',')
           FROM       managers m
           WHERE      m.essay_id = ad.essay_id
           CONNECT BY m.rn = PRIOR m.rn + 1 AND m.essay_id = PRIOR m.essay_id
           START WITH m.rn = 1
         ) AS manager
FROM     all_data ad
ORDER BY ad.essay_id;

重要的一点是，分配行号的每个子查询都使用自己的内联视图来获得不同的值。因此，对于获取以下内容的相同数据：

  ESSAY_ID WRITER     MANAGER  
---------- ---------- ----------
       123 abc,xyz    lmo,pqr   
       456 abc,def    lmo       

SQL Fiddle。