复杂 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 可用逗号连接成员。

结果如下:

imagepng

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