Tengo tres entidades: departamento, empleado e informe. Un departamento tiene muchos empleados, cada uno de los cuales tiene muchos informes. Quiero seleccionar un empleado en cada departamento que tenga más informes. No tengo idea de cómo comenzar esta consulta. Esta pregunta parece muy similar, pero no puedo entender cómo manipular esas respuestas para lo que quiero.

Tengo acceso completo a todo el sistema, por lo que puedo hacer los cambios necesarios. En caso de empate, es seguro elegir arbitrariamente uno de los resultados.

Departamento:

 ID | Name
----|------
  1 | DeptA
  2 | DeptB
  3 | DeptC
  4 | DeptD

Empleada:

 ID | Name | DeptID
----|------|--------
  1 | Joe  | 1
  2 | John | 1
  3 | Emma | 2
  4 | Jack | 3
  5 | Sven | 3
  6 | Axel | 4
  7 | Brad | 4
  8 | Jane | 4

Reporte:

 ID | EmployeeID
----|------------
  1 | 1
  2 | 2
  3 | 3
  4 | 5
  5 | 6
  6 | 6
  7 | 8

Resultado deseado (suponiendo que solo haya consultado nombres):

Joe OR John (either is acceptable)
Emma
Sven
Axel
0
vaindil 28 dic. 2016 a las 03:15

3 respuestas

La mejor respuesta

¿Cómo iniciar esta consulta? Bueno, obtenga la información sobre cada empleado, el departamento y la cantidad de informes:

select e.name, e.deptid, count(*) as numreports
from employee e join
     reports r
     on e.id = r.employeeid
group by e.name, e.deptid;

Ahora solo desea el recuento más grande en cada departamento. Sugeriría row_number() o rank() dependiendo de cómo quieras manejar los lazos:

select er.*
from (select e.name, e.deptid, count(*) as numreports,
             row_number() over (partition by e.deptid order by count(*) desc) as seqnum
      from employee e join
           reports r
           on e.id = r.employeeid
      group by e.name, e.deptid
     ) er
where seqnum = 1;

Si desea el nombre del departamento en lugar del número, también puede unirlo.

5
Gordon Linoff 28 dic. 2016 a las 00:26

Por favor, intente el siguiente código: -

SELECT D.NAME
FROM (
    SELECT C.NAME, RANK() OVER (
            PARTITION BY C.DEPTID ORDER BY C.COUNTS DESC
            ) RNK
    FROM (
        SELECT EMPID, NAME, COUNT(EMPID) AS COUNTS, DEPTID
        FROM DBO.REPORT AS A
        JOIN DBO.EMPLO AS B ON A.EMPID = B.ID
        GROUP BY EMPID, NAME, DEPTID
        ) AS C
    ) AS D
WHERE D.RNK = 1
0
Shakeer Mirza 30 dic. 2016 a las 04:10

De su pregunta, el esquema será

SELECT *  into  #Department FROM(
select 1 ID,'DEPTA' NAME
UNION ALL
select 2,'DEPTB'
UNION ALL
select 3,'DEPTC'
UNION ALL
select 4,'DEPTD')TAB

SELECT * INTO #Employee FROM (

SELECT  1 ID  ,'Joe' Name ,  1 DeptID
UNION ALL
SELECT 2 , 'John'  , 1
UNION ALL
SELECT 3 , 'Emma'  ,2
UNION ALL
SELECT 4  ,'Jack' , 3
UNION ALL
SELECT 5  ,'Sven' , 3
UNION ALL
SELECT 6 , 'Axel' , 4
UNION ALL
SELECT 7  ,'Brad' , 4
UNION ALL
SELECT 8  ,'Jane' , 4)AS A

SELECT * INTO  #Report FROM(
SELECT 1 ID  ,1 EmployeeID
UNION ALL
SELECT 2,  2
UNION ALL
SELECT 3  ,3
UNION ALL
SELECT 4,  5
UNION ALL
SELECT 5,  6
UNION ALL
SELECT 6,  6
UNION ALL
SELECT 7,  8
UNION ALL
SELECT 8,  8
UNION ALL
SELECT 9,  8
)AS A

Y debe aplicar DENSE_RANK() para otorgar un rango basado en el no de informes (recuento)

;WITH CTE AS(
select DEP.ID DEP_ID, DEP.NAME DEP,EMP.ID EMP_ID, EMP.Name EMP
,DENSE_RANK() OVER(PARTITION BY DEP.ID ORDER BY  COUNT(REP.ID)  DESC) REP_RANK
,COUNT(REP.ID) NO_OF_REP FROM #Department DEP
inner join #Employee emp on emp.deptid=dep.id
inner join #report rep on rep.EmployeeID=emp.id
GROUP BY DEP.ID, DEP.NAME ,EMP.ID, EMP.Name 
)
SELECT DEP, EMP, NO_OF_REP FROM CTE WHERE REP_RANK=1

Aquí en el DEPTA, Joe y John serán elegidos porque ambos tienen 1 recuento de informes, que es un recuento máximo en DEPTA.

Y el resultado será

+-------+------+-----------+
|  DEP  | EMP  | NO_OF_REP |
+-------+------+-----------+
| DEPTA | Joe  |         1 |
| DEPTA | John |         1 |
| DEPTB | Emma |         1 |
| DEPTC | Sven |         1 |
| DEPTD | Jane |         3 |
+-------+------+-----------+
2
Shakeer Mirza 28 dic. 2016 a las 05:45