如何在不使用它的情况下执行listagg功能 [英] How to perform listagg functionality without using it

问题描述

我只想创建一个查询，以便它在oracle中执行与listagg相同的功能，但我不想使用wm_cocat，lag或lead。我的桌子是 -



Emp_id - emp_name - 部门

1 - alan - 1

2 - bradley - 1

3 - sarah - 2

4 - 将2

5 - bale - 2

6 - doug - 3

7 - hagar - 3

8 - carla - 4

9 - 大卫 - 4

10 - vashi - 1



我有一个查询正在运行，但我不太确定它是如何工作的。查询是 -



{

SELECT部门，

LTRIM（最大值（SYS_CONNECT_BY_PATH（emp_name，'， '））

保持（由emp_name进行DENSE_RANK最后订购），'，'）AS员工

FROM（选择部门，

emp_name，

ROW_NUMBER（）OVER（PARTITION BY部门ORDER BY emp_name）AS emp，

ROW_NUMBER（）OVER（按部门划分ORDER BY emp_name）-1 AS prev

来自employee4）

GROUP BY部门

CONNECT BY部门=先前部门AND prev = PRIOR emp

START WITH emp = 1 ; }



任何人都可以解释一下这个查询是如何工作的，并且是他们的替代解决方案吗？

I just want to create a query so that it does the same function as listagg in oracle but I dont want to use wm_cocat, lag or lead. My table is-

Emp_id - emp_name - department
1 - alan - 1
2 - bradley - 1
3 - sarah - 2
4 - will 2
5 - bale - 2
6 - doug - 3
7 - hagar - 3
8 - carla - 4
9 - david - 4
10 - vashi - 1

I have a query which is working but I am not quite sure how it work. The query is-

{
SELECT department,
LTRIM(max(SYS_CONNECT_BY_PATH(emp_name,','))
KEEP (DENSE_RANK LAST ORDER BY emp_name),',')AS employees
FROM (SELECT department,
emp_name,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY emp_name) AS emp,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY emp_name) -1 AS prev
FROM employee4)
GROUP BY department
CONNECT BY department=prior department AND prev = PRIOR emp
START WITH emp = 1; }

Can anyone explain me how this query works and is their any alternative solution for that?

推荐答案

使用COMMON TABLE EXPRESSION我们可以做这样的事情 -

WITH COMMON TABLE EXPRESSION we can do something like this-

with t (emp_name, department, rn) as ( select emp_name, department,row_number()
over (partition by department order by emp_name) as rn
        from employee8),
        c (emp_name, department, rn) as (select  emp_name,department, rn from t where rn = 1
        union all
        select (c1.emp_name) ||', '|| t1.emp_name, t1.department, c1.rn+1
        from t t1 , c c1
        where t1.department = c1.department
          and c1.rn + 1 = t1.rn
)
select emp_name, department
from c c1
where rn = (select max(rn)
            from c c2
            where c1.department = c2.department)
            order by department;

通过使用dense_rank和rownumber我们可以这样做 -

By using dense_rank and rownumber we can do it like-

with t (emp_name, department, rn) as ( select emp_name, department,row_number()
over (partition by department order by emp_name) as rn
        from employee8),
        c (emp_name, department, rn) as (select  emp_name,department, rn from t where rn = 1
        union all
        select (c1.emp_name) ||', '|| t1.emp_name, t1.department, c1.rn+1
        from t t1 , c c1
        where t1.department = c1.department
          and c1.rn + 1 = t1.rn
)
select emp_name, department
from c c1
where rn = (select max(rn)
            from c c2
            where c1.department = c2.department)
            order by department;

这篇关于如何在不使用它的情况下执行listagg功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！