从SQL中以逗号分隔的ID中获取名称 [英] get names from comma separated ids in SQL

问题描述

我在Oracle中有这种表

I have this kind of table in Oracle

empid name deptid
1      a    1,2
2      b
3      c    1,2,3

我的部门表如下，

Deptid DeptName
 1      IT
 2      Finance
 3      HR

我希望在select语句中得到这样的结果

i want result like this in select statement,

empid name dept
1     a    IT,Finance
2     b
3     c    IT,Finance,HR

两个表中都有1000多个行，我是pl \ sql的初学者，不知道该怎么做，请帮忙.

i have 1000+ rows in both tables, i am beginner to pl\sql and don't know how to do this, please help.

推荐答案

为此，需要使用整数连接表，以使每个员工行的出现频率与其字符串中的部门ID相同，但至少要出现一次.对于联接结果中的行，数字 i 从1到 n ，其中 n 是该员工字符串中ID的数量(如果有该员工的任何部门ID).然后，您可以使用REGEXP_SUBSTR()从字符串中获取第_i_th个数字.使用它离开部门，以获取部门名称.然后使用LISTAGG()进行汇总，以再次为每位员工获得一行.

To do that join a table with integers, so that every employee row occurs as often as there are department IDs in its string but at least one time. For the rows in the join result the numbers i go from 1 to n, where n is the number of IDs in the string for that employee (if there are any department IDs for the employee). Then you can use REGEXP_SUBSTR() to get the _i_th number from the string. Use that to left join the departments, to get the department name. Then use an aggregation using LISTAGG() to get a single row for each employee again.

SELECT E.EMPID,
       E.NAME,
       E.DEPTID,
       LISTAGG(D.DEPTNAME, ',') WITHIN GROUP (ORDER BY I.I) DEPTNAME
       FROM EMPLOYEE E
            LEFT JOIN (SELECT ROW_NUMBER() OVER (ORDER BY DEPTID) I
                              FROM DEPARTMENT) I
                      ON I.I <= REGEXP_COUNT(E.DEPTID, ',') + 1
            LEFT JOIN DEPARTMENT D
                      ON D.DEPTID = TO_NUMBER(REPLACE(REGEXP_SUBSTR(',' || E.DEPTID, ',([[:digit:]]+)', 1, I.I), ',', ''))
       GROUP BY E.EMPID,
                E.NAME,
                E.DEPTID;

db<>小提琴

这篇关于从SQL中以逗号分隔的ID中获取名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！