复杂 SQL 的组成员合并问题

在创建Jasper报告的Oracle 10g查询中,面临一个问题:需要从多对一的关系中获取每个论文(essay)的作者和他们的经理名称,并以特定格式展示。当前查询返回的作者通过逗号分隔,但需增加一个“manager”列。解决方案可能涉及到在SQL中处理复杂的有序运算,或者使用如SPL的工具,以更简洁的方式完成任务。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

【问题】

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 的组成员合并问题  
分组后合并成员  

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值