Sélectionner des utilisateurs appartenant uniquement à des départements particuliers

Je sais que cette question a déjà été répondu, mais c'était un problème amusant à faire et j'ai essayé de faire en sorte que personne d'autre ne l'a fait. Mes avantages sont que vous pouvez entrer n'importe quelle liste de chaînes tant que chaque valeur a une virgule après et que vous n'avez pas à vous soucier de vérifier les comptes.

Remarque: Les valeurs doivent être listées par ordre alphabétique.

Solution XML avec application croisée

select DISTINCT empID
FROM employe A
CROSS APPLY
            (
                SELECT department + ','
                FROM employe B
                WHERE A.empID = B.empID
                ORDER BY department
                FOR XML PATH ('')
            ) CA(Deps)
WHERE deps = 'Y,Z,'

Résultats:

empID
----------
A103

Pour la condition 1: z et y

 select z.empID from (select empID from employe where department = 'z' ) as z
inner join (select empID from employe where department = 'y' )  as y 
on z.empID = y.empID
where z.empID Not in(select empID from employe where department = 'x' ) 

Pour la condition 1: z et x

select z.empID from (select empID from employe where department = 'z' ) as z
inner join (select empID from employe where department = 'x' )  as x 
on z.empID = x.empID
where z.empID Not in(select empID from employe where department = 'y' )

Pour la condition 1: z, y et x

select z.empID from (select empID from employe where department = 'z' ) as z
inner join (select empID from employe where department = 'x' )  as x 
on z.empID = x.empID
inner join (select empID from employe where department = 'y' )  as y on 
y.empID=Z.empID

, Vous pouvez utiliser GROUP BY avec having comme ceci. SQL Violon

SELECT empID 
FROM employe
GROUP BY empID
HAVING SUM(CASE WHEN department= 'Y' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN department= 'Z' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN department NOT IN('Y','Z') THEN 1 ELSE 0 END) = 0

, Sans GROUP BY et Having

SELECT empID 
FROM employe E1
WHERE (SELECT COUNT(DISTINCT department) FROM employe E2 WHERE E2.empid = E1.empid and  department IN ('Z','Y')) = 2
EXCEPT
SELECT empID 
FROM employe
WHERE department NOT IN ('Z','Y')

Si vous souhaitez utiliser l'une des requêtes ci-dessus avec d'autres tables en utilisant une jointure, vous pouvez utiliser CTE ou une table dérivée comme celle-ci.

;WITH CTE AS 
(

    SELECT empID 
    FROM employe
    GROUP BY empID
    HAVING SUM(CASE WHEN department= 'Y' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN department= 'Z' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN department NOT IN('Y','Z') THEN 1 ELSE 0 END) = 0
)
SELECT cols from CTE join othertable on col_cte = col_othertable

Essayez ceci

select empID from employe 
where empId in (select empId from employe 
where department = 'Z' and department = 'Y') 
and empId not in (select empId from employe 
where department = 'X') ;

Pour si la condition est: Z et Y

   SELECT EMPID FROM EMPLOYE WHERE DEPARTMENT='Z'  AND 
   EMPID IN (SELECT EMPID FROM EMPLOYE WHERE DEPARTMENT ='Y')AND
   EMPID NOT IN(SELECT EMPID FROM EMPLOYE WHERE DEPARTMENT NOT IN ('Z','Y'))

La requête suivante fonctionne lorsque vous voulez des employés des départements ' Y ' et ' Z 'et non 'X'.

select empId from employe 
where empId in (select empId from employe 
                where department = 'Z') 
and empId in (select empId from employe 
              where department = 'Y') 
and empId not in (select empId from employe 
                  where department = 'X') ;

Pour votre deuxième cas, remplacez simplement not in par in dans la dernière condition.

Essayez ceci,

SELECT  a.empId
FROM    employe a
        INNER JOIN
        (
            SELECT  empId
            FROM    employe 
            WHERE   department IN ('X', 'Y', 'Z')
            GROUP   BY empId
            HAVING  COUNT(*) = 3
           )b ON a.empId = b.empId
GROUP BY a.empId

Compte doit basé sur le nombre de conditions.

Vous peut aussi utiliser GROUP BY et HAVING, vous avez juste besoin de le faire dans une sous-requête.

Par exemple, nous allons commencer avec une simple requête pour trouver tous les employés dans les ministères X et Y (et pas dans les autres départements):

SELECT empID,
  GROUP_CONCAT(DISTINCT department ORDER BY department ASC) AS depts
FROM emp_dept GROUP BY empID
HAVING depts = 'X,Y'

J'ai utilisé MySQL GROUP_CONCAT() Fonctionne comme un raccourci pratique ici, mais vous pourriez obtenir les mêmes résultats sans cela, par exemple comme ceci:

SELECT empID,
  COUNT(DISTINCT department) AS all_depts,
  COUNT(DISTINCT CASE
    WHEN department IN ('X', 'Y') THEN department ELSE NULL
  END) AS wanted_depts
FROM emp_dept GROUP BY empID
HAVING all_depts = wanted_depts AND wanted_depts = 2

Maintenant, pour combiner cela avec une autre condition de requête, simplement prenez une requête qui inclut les autres conditions, et joignez votre table employees à la sortie de la requête ci-dessus :

SELECT empID, name, depts
FROM employees
JOIN (
    SELECT empID,
      GROUP_CONCAT(DISTINCT department ORDER BY department ASC) AS depts
    FROM emp_dept GROUP BY empID
    HAVING depts = 'X,Y'
  ) AS tmp USING (empID)
WHERE -- ...add other conditions here...

Voici un SQLFiddle démontrant cette requête.

Ps. La raison pour laquelle vous devriez utiliser une sous-requête JOIN au lieu d'une sous-requête IN pour cela est que MySQL n'est pas si bon pour optimiser les sous-requêtes IN.

Plus précisément (à partir de v5. 7, au moins), MySQL convertit toujours les sous-requêtes IN en dépendante les sous-requêtes, de sorte que la sous-requête doit être ré-exécuté pour chaque ligne de la requête externe, même si la sous-requête est indépendante. Par exemple, la requête suivante (à partir de la documentation liée ci-dessus):

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

Est effectivement converti en:

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

Ce peut être encore raisonnablement rapide, si t2 est petit et / ou a un index permettant des recherches rapides. Toutefois, si, comme dans l'exemple ci-dessus) de l'exécution de la sous-requête peut prendre un beaucoup de travail, la performance peut souffrir mal. L'utilisation d'un JOIN à la place permet à la sous-requête d'être exécutée une seule fois, et offre donc généralement de bien meilleures performances.

Qu'en est-il d'une auto-jointure? (Conforme à la norme ANSI-travaillé depuis plus de 20 ans)

SELECT * FROM employee e JOIN employee e2 ON e.empid = e2.empid
WHERE e.department = 'x' AND e2.department ='y'

Cela montre que a101 et a104 travaillent tous deux dans les deux départements.

Solution utilisant where clause:

select distinct e.empID
from employe e
where exists( select * 
              from employe
              where empID = e.empID
              having count(department) = count(case when department in('Y','X','Z') then department end)
                 and count(distinct department) = 3)

exists vérifie s'il existe des enregistrements pour des EmpId spécifiques dont le nombre total de departmentest égal au nombre conditionnel de departmentcorrespondants et qu'il est également égal au nombre de departmentfourni à la clause in. Il convient également de mentionner que nous appliquons ici la clause having sans la clause group by, sur l'ensemble, mais avec déjà spécifié, un seul empID.

SQLFiddle

Vous pouvez y parvenir sans la sous-requête corrélée, mais avec la clause group by:

select e.empId
from employe e
group by e.empID
having count(department) = count(case when department in('Y','X','Z') then department end)
   and count(distinct department) = 3

SQLFiddle

Vous pouvez également utiliser une autre variante de la clause having pour la requête ci-dessus:

having count(case when department not in('Y','X', 'Z') then department end) = 0
   and count(distinct case when department in('Y','X','Z') then department end) = 3

SQLFiddle

Dans Postgres cela peut être simplifié en utilisant des tableaux:

select empid
from employee
group by empid
having array_agg(department order by department)::text[] = array['Y','Z'];

, Il est important de trier les éléments dans le array_agg() et de les comparer à une liste triée des ministères dans le même ordre. Sinon, cela ne retournera pas les bonnes réponses.

Par exemple, {[6] } pourrait potentiellement renvoyer de mauvais résultats.

Cela peut être fait de manière plus souple en utilisant un CTE pour fournir les départements:

with depts_to_check (dept) as (
   values ('Z'), ('Y')
)
select empid
from employee
group by empid
having array_agg(department order by department) = array(select dept from depts_to_check order by dept);

De cette façon, le tri des éléments est toujours effectué par la base de données et être cohérent entre les valeurs du tableau agrégé et celle à laquelle il est comparé.

Une option avec SQL standard consiste à vérifier si au moins une ligne a un département différent en comptant toutes les lignes

select empid
from employee
group by empid
having min(case when department in ('Y','Z') then 1 else 0 end) = 1
  and count(case when department in ('Y','Z') then 1 end) = 2;

La solution ci-dessus ne fonctionnera pas s'il est possible qu'un seul employé soit affecté deux fois au même département!

Le having min (...) peut être simplifié dans Postgres en utilisant l'agrégat bool_and().

Lors de l'application de la norme filter() condition à faire agrégation conditionnelle cela peut également être fait pour travailler avec la situation où un employé peut être affecté au même ministère deux fois

select empid
from employee
group by empid
having bool_and(department in ('Y','Z'))
  and count(distinct department) filter (where department in ('Y','Z')) = 2;

bool_and(department in ('Y','Z')) renvoie true uniquement si la condition est true pour toutes les lignes du groupe.

Une autre solution avec SQL standard consiste à utiliser l'intersection entre les employés qui ont au moins ces deux départements et ceux qui sont affectés à exactement deux départements:

-- employees with at least those two departments
select empid
from employee
where department in name in ('Y','Z')
group by empid
having count(distinct department) = 2

intersect

-- employees with exactly two departments
select empid
from employee
group by empid
having count(distinct department) = 2;