复杂 SQL 的组成员合并问题

【问题】

I am writing an oracle 10g query for creating jasper reports.

This is the query –

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

The essays are unique but can have multiple writers (essay_writer_join) This query gives me essays with the writers which are separated by comma.

The problem is I need to add one more column called “manager” that will show the manager of the writer. The manager information is in the WRITER_TABLE with column name “manager_name”. The essay_table has the writers first name, last name and username. The tricky part is that 2 writers can have 2 different managers. For example for essay ‘123’ the writers are ‘abc’ and ‘xyz’ and the managers for these writers are ‘lmo’ and ‘pqr’ respectively then the records should indicate in following format

essay id    writer       manager
123         abc, xyz     lmo, pqr
456         abc, def     lmo

Is this possible in oracle 10g sql? I tried to search for similar situation but cannot find any related solutions.

【回答】

用 SQL 处理这种有序运算太麻烦了，给报表工具提供数据，可以用 SPL 来做，代码相较于 SQL 简单易懂，写法如下：

	A
1	$select   CASE  NVL(u.firstname, 'NULL FIRSTNAME') WHEN 'NULL FIRSTNAME' THEN u.username                               ELSE (u.firstname + u.lastname)                            END                                 AS full_name, u.manager manager,a.id essay_id  from    writer_table u  join   essay_writer_join ej   on      u.id=ej.user_id   join     essay_table a on    ej.essay_id=a.id
2	=A1.group(essay_id   ;~.(full_name).concat@c():writer,~.id(manager).concat@c():manager)

其中，

A1：用 join 语句连接三张表

A2：按 essay_id 分组，将组内成员用逗号连接。其中 ~ 表示每组记录，函数 concat@c 可用逗号连接成员。

结果如下：

集算器可以作为插件集成到 Jasper 中，详细可参考【JasperReport 调用 SPL 脚本】

相关文章：

SQL 难点解决：集合及行号

例子程序：

如何将两列数据转为一列数据  
对分组后的各组前几名做合并 
复杂 SQL 的组成员合并问题  
分组后合并成员