不使用 listagg 功能的查詢

我想編寫一個查詢，以便它在 oracle 中執行 listagg 的功能，但我不想使用它。實際上我也不想要任何像滯後和領先這樣的功能。我的桌子是——

Emp_id                          Emp_name                        Department
1                               bradley                             1 
2                                david                              1  
3                                 will                              2
4                                 john                              2
5                                 bale                              2
6                                 alan                              3
7                                 doug                              3
8                                 hagar                             4
9                                 carla                             4
10                                vashi                             1

我想要這樣的輸出-

department                               emp_name
1                                        bradley,david, vashi
2                                         will ,john ,bale
3                                          alan, doug
4                                          hagar, carla

我已經對此進行了查詢，但在這裡我不想使用 sys_connect_by_path。我的查詢是-

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 prev = PRIOR emp AND department = PRIOR department
START WITH emp = 1;

誰能幫我？

您可以使用遞歸公用表表達式。我使用了一個 tmp 表來列舉員工：

with tmp (empname, department, rn) as (
       select empname, department
            , row_number() over (partition by department 
                                 order by empname) as rn 
       from employees
), cte (empname, department, rn) as ( 
       select cast(empname as varchar(100)) as empname
            , department, rn 
       from tmp 
       where rn = 1 
       union all 
       select rtrim(c.empname) || ', ' || t.empname
            , t.department, c.rn+1 
       from tmp t
          , cte c 
       where t.department = c.department 
         and c.rn + 1 = t.rn
) 
select empname, department 
from cte c1 
where rn = (select max(rn) 
           from cte c2 
           where c1.department = c2.department)

引用自：https://dba.stackexchange.com/questions/110644