Sql-Standard
不使用 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)