复杂 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 脚本】
相关文章：
例子程序：
