resueltos - numero de empleados por departamento sql

Selecciona usuarios que pertenezcan solo a departamentos particulares

Question

Tengo la siguiente tabla con dos campos, a saber, a y b, como se muestra a continuación:

create table employe
(
    empID varchar(10),
    department varchar(10)
);

Insertando algunos registros:

insert into employe values(''A101'',''Z''),(''A101'',''X''),(''A101'',''Y''),(''A102'',''Z''),(''A102'',''X''),
             (''A103'',''Z''),(''A103'',''Y''),(''A104'',''X''),(''A104'',''Y''),(''A105'',''Z''),(''A106'',''X'');


select * from employe;

empID   department
------------------
A101    Z
A101    X
A101    Y
A102    Z
A102    X
A103    Z
A103    Y
A104    X
A104    Y
A105    Z
A106    X

Nota : Ahora quiero mostrarle al empleado quién es único y solo pertenece al departamento Z e Y Por lo tanto, de acuerdo con la condición, el único empleado A103 debe mostrarse debido a que solo pertenece al departamento Z e Y Pero el empleado A101 no debería aparecer porque pertenece a Z,X, and Y

Resultado esperado :

Si la condición es: Z e Y entonces el resultado debería ser:

empID
------
A103

Si la condición es: Z y X , el resultado debería ser:

empID
------
A102

Si la condición es: Z , X e Y entonces el resultado debería ser:

empID
------
A101

Nota : quiero hacerlo solo en la cláusula where (no quiero usar el group by y having cláusulas), porque voy a incluir este en el otro where también.

Answer 1

¿Qué tal un auto unirse? (Cumple con ANSI - trabajó por más de 20 años)

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

Esto demuestra que a101 y a104 trabajan ambos en ambos departamentos.

Answer 2

En Postgres esto se puede simplificar utilizando matrices:

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

Es importante ordenar los elementos en el array_agg() y compararlos con una lista ordenada de departamentos en el mismo orden. De lo contrario, esto no devolverá las respuestas correctas.

Por ejemplo, array_agg(department) = array[''Z'', ''Y''] podría potencialmente devolver resultados erróneos.

Esto se puede hacer de una manera más flexible utilizando un CTE para suministrar a los departamentos:

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 esa manera, la base de datos siempre realiza la clasificación de los elementos y será coherente entre los valores de la matriz agregada y la que se compara.

Una opción con SQL estándar es verificar si al menos una fila tiene un departamento diferente junto con el conteo de todas las filas

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 solución anterior no funcionará si es posible que un solo empleado sea asignado dos veces al mismo departamento!

El having min (...) se puede simplificar en Postgres utilizando el agregado bool_and() .

Al aplicar la condición de filter() estándar para realizar una agregación condicional, también se puede hacer que funcione en una situación en la que un empleado puede ser asignado al mismo departamento dos veces.

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'')) solo devuelve true si la condición es verdadera para todas las filas del grupo.

Otra solución con SQL estándar es usar la intersección entre los empleados que tienen al menos esos dos departamentos y los que están asignados a exactamente dos departamentos:

-- 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;

Answer 3

Esta es una División Relacional sin Resto (RDNR) problema. Vea este article de Dwain Camps que brinda muchas soluciones a este tipo de problema.

Primera solucion

SQL Fiddle

SELECT empId
FROM (
    SELECT
        empID, cc = COUNT(DISTINCT department)
    FROM employe
    WHERE department IN(''Y'', ''Z'')
    GROUP BY empID
)t
WHERE
    t.cc = 2
    AND t.cc = (
        SELECT COUNT(*)
        FROM employe
        WHERE empID = t.empID
    )

Segunda solucion

SQL Fiddle

SELECT e.empId
FROM employe e
WHERE e.department IN(''Y'', ''Z'')
GROUP BY e.empID
HAVING
    COUNT(e.department) = 2
    AND COUNT(e.department) = (SELECT COUNT(*) FROM employe WHERE empID = e.empId)

Sin usar GROUP BY Y HAVING :

SELECT DISTINCT e.empID
FROM employe e
WHERE
    EXISTS(
        SELECT 1 FROM employe WHERE department = ''Z'' AND empID = e.empID
    )
    AND EXISTS(     
        SELECT 1 FROM employe WHERE department = ''Y'' AND empID = e.empID
    )
    AND NOT EXISTS(
        SELECT 1 FROM employe WHERE department NOT IN(''Y'', ''Z'') AND empID = e.empID
    )

Answer 4

La siguiente consulta funciona cuando desea empleados de los departamentos ''Y'' y ''Z'' y no ''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'') ;

Para su segundo caso, simplemente sustitúyalo en la última condición.

Answer 5

Para la condición 1: z y 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'' ) 

Para la condición 1: z y 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'' )

Para la condición 1: z, y y 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

Answer 6

Prueba esto,

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

El conteo debe basarse en el número de condiciones.

Answer 7

Puedes usar GROUP BY con having así. SQL Fiddle

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

Sin GROUP BY Y 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 desea utilizar cualquiera de las consultas anteriores con otras tablas usando una combinación, puede usar CTE o una tabla derivada como esta.

;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

Answer 8

Sé que esta pregunta ya ha sido respondida, pero fue un problema divertido de hacer y traté de hacerlo de una manera que nadie más lo ha hecho. El beneficio mío es que puede ingresar cualquier lista de cadenas siempre que cada valor tenga una coma después y no tenga que preocuparse por verificar los conteos.

Nota: Los valores deben estar listados en orden alfabético.

Solución XML con CROSS APPLY

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,''

Resultados:

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

Answer 9

Solución usando la cláusula where :

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 verificación si hay registros para EmpId específicos que tienen un recuento total de department igual al conteo condicional de solo department coincidentes y que también es igual al número de department proporcionados a la cláusula in . También vale la pena mencionar que aquí aplicamos la cláusula have sin la cláusula group by , en todo el conjunto, pero con una especificación ya especificada, solo una.

SQLFiddle

Puede lograr esto sin la subconsulta correlacionada, pero con la cláusula 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

También puede usar otra variación de having cláusula para la consulta anterior:

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

Answer 10

También puede usar GROUP BY y HAVING ; solo necesita hacerlo en una subconsulta.

Por ejemplo, comencemos con una consulta simple para encontrar a todos los empleados en los departamentos X e Y (y no en otros departamentos):

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

He utilizado la función GROUP_CONCAT() MySQL como un atajo conveniente aquí, pero también podría obtener los mismos resultados sin ella, por ejemplo, de esta manera:

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

Ahora, para combinar esto con otra condición de consulta, simplemente tome una consulta que incluya las otras condiciones, y combine la tabla de sus empleados con el resultado de la consulta anterior :

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...

Aquí hay un SQLFiddle que demuestra esta consulta.

PD. La razón por la que debería usar una JOIN lugar de una subconsulta IN para esto es porque MySQL no es tan bueno para optimizar las subconsultas IN .

Específicamente (a partir de v5.7, al menos), MySQL siempre convierte las subconsultas IN en subconsultas dependientes , de modo que la subconsulta debe volver a ejecutarse para cada fila de la consulta externa, incluso si la subconsulta original era independiente. Por ejemplo, la siguiente consulta (de la documentación vinculada anteriormente):

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

se convierte efectivamente en:

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

Esto todavía puede ser razonablemente rápido, si t2 es pequeño y / o tiene un índice que permite búsquedas rápidas. Sin embargo, si (como en el ejemplo original anterior) la ejecución de la subconsulta puede requerir mucho trabajo, el rendimiento puede sufrir mucho. En su lugar, usar JOIN permite que la subconsulta se ejecute solo una vez y, por lo tanto, generalmente ofrece un rendimiento mucho mejor.

Answer 11

para Si la condición es: Z e 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''))

Answer 12

prueba esto

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'') ;