复杂 SQL 的组成员合并问题

润乾软件

于 2022-02-07 11:56:42 发布

阅读量283

点赞数

分类专栏： JAVA计算文章标签： sql 数据库关联分组数据合并

本文链接：https://blog.csdn.net/raqsoft/article/details/122806775

版权

在创建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