复杂 SQL 的组成员合并问题

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

【问题】

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值