J'ai deux tables, par exemple "employés" et "projets". J'ai besoin d'une liste de tous les projets avec le nom de tous les employés impliqués.
Le problème est maintenant, que les employeurs sont enregistrés avec des virgules, comme dans l'exemple ci-dessous: Comment dois-je écrire la jointure pour obtenir la sortie souhaitée: P> project | employe
--------------------
Project X | Person B,
Project Y |
Project Z | Person A, Person C
4 Réponses :
Vous pouvez essayer acclamations !! groupe par et listagg comme suit:
Voici une sorte de façon amusante de le faire:
WITH cteId_counts_by_project AS (SELECT PROJECT,
NVL(REGEXP_COUNT(EMPLOYE_ID, '[^,]'), 0) AS ID_COUNT
FROM PROJECTS),
cteMax_id_count AS (SELECT MAX(ID_COUNT) AS MAX_ID_COUNT
FROM cteId_counts_by_project),
cteProject_employee_ids AS (SELECT PROJECT,
EMPLOYE_ID,
REGEXP_SUBSTR(EMPLOYE_ID, '[^,]',1, LEVEL) AS ID
FROM PROJECTS
CROSS JOIN cteMax_id_count m
CONNECT BY LEVEL <= m.MAX_ID_COUNT),
cteProject_emps AS (SELECT DISTINCT PROJECT, ID
FROM cteProject_employee_ids
WHERE ID IS NOT NULL),
cteProject_empnames AS (SELECT pe.PROJECT, pe.ID, e.EMPLOYE
FROM cteProject_emps pe
LEFT OUTER JOIN EMPLOYEES e
ON e.ID = pe.ID
ORDER BY pe.PROJECT, e.EMPLOYE)
SELECT p.PROJECT,
LISTAGG(pe.EMPLOYE, ',') WITHIN GROUP (ORDER BY pe.EMPLOYE) AS EMPLOYEE_LIST
FROM PROJECTS p
LEFT OUTER JOIN cteProject_empnames pe
ON pe.PROJECT = p.PROJECT
GROUP BY p.PROJECT
ORDER BY p.PROJECT
Vous pouvez extraire les identifiants numériques à partir de la colonne code> Employee_id CODE> de la table CODE> Projets CODE> à l'aide de Regexp_substr () Code> et RTRIM () Code> ( Couper la dernière virgule supplémentaire em>) fonctionne ensemble, puis concaténate de Listagg () Code> Fonction: with p2 as
(
select distinct p.*, regexp_substr(rtrim(p.employee_id,','),'[^,]',1,level) as p_eid,
level as rn
from projects p
connect by level <= regexp_count(rtrim(p.employee_id,','),',')
)
select p2.project, listagg(e.employee,', ') within group (order by p2.rn) as employee
from p2
left join employees e on e.id = p2.p_eid
group by p2.project
Bonjour Barbaros, votre code fonctionne bien pour 1 ou 2 employés, mais pas avec 3 ou plus.
Salut @philipp, tu as raison. J'ai réparé le changement de style.
Une autre option; Code dont vous avez besoin (comme vous avez déjà ces tables) commence à la ligne n ° 12:
SQL> -- Your sample data SQL> with employees (employee, id) as 2 (select 'Person A', 1 from dual union all 3 select 'Person B', 2 from dual union all 4 select 'Person C', 3 from dual 5 ), 6 projects (project, employee_id) as 7 (select 'Project X', ',2,' from dual union all 8 select 'Project Y', null from dual union all 9 select 'Project Z', ',1,3,' from dual 10 ), 11 -- Employees per project 12 emperpro as 13 (select project, regexp_substr(employee_id, '[^,]+', 1, column_value) id 14 from projects cross join table(cast(multiset(select level from dual 15 connect by level <= regexp_count(employee_id, ',') + 1 16 ) as sys.odcinumberlist)) 17 ) 18 -- Final result 19 select p.project, listagg(e.employee, ', ') within group (order by null) employee 20 from emperpro p left join employees e on e.id = p.id 21 group by p.project 22 / PROJECT EMPLOYEE --------- ---------------------------------------- Project X Person B Project Y Project Z Person A, Person C SQL>
Vous ne devriez pas stocker des valeurs séparées par des virgules pour commencer. Avez-vous la possibilité de résoudre ce modèle de données cassé?
Ce n'est pas ma base de données. Malheureusement, je dois vivre et travailler avec ça :-(
J'imagine que vous allez devoir écrire un script qui tire les identifiants séparés des virgules, supprime les virgules stockant les résultats dans un tableau que vous pouvez ensuite exécuter contre l'autre table. Bien que je parle au client de changer la façon dont ils stockent les données pour suivre les meilleures pratiques.
"Personne C" est associé à ID = 3. Pourquoi avez-vous cette personne qui se présente dans les résultats pour "projet z"?
Oh tu as raison. J'ai réparé l'exemple :-)