列出所有至少拥有 3 名员工的老板

Question

如何查询下表获取所有boss的姓名（即boss_id=staff_id） 谁至少有 3 名员工？我是否使用having、group by、...？

    CREATE TABLE STAFF (STAFF_ID                    CHAR(3),
                        STAFF_NAME              CHAR(20),
                        GENDER                  CHAR(6),
                        DEPARTMENT              CHAR(20),
                        BOSS_ID                 CHAR(3)
                        SALARY                  NUMBER(8,2))

谢谢！

Answer 1

我使用 COUNT(*)>3 因为我假设，您的意思是除老板本人之外的 3 名员工。

SELECT a.staff_name
FROM staff a
INNER JOIN (
  SELECT boss_id,COUNT(*) AS cnt_staff FROM staff GROUP BY boss_id HAVING COUNT(*)>3
) b ON a.boss_id=b.boss_id
WHERE a.staff_id=a.boss_id

Answer 2

select S.Boss_ID, X.STAFF_NAME
from STAFF S cross apply (Select staff_name from STAFF S2 where S2.staff_id=S.boss_ID) X
group by S.Boss_ID, X.STAFF_NAME
having count(*)>=3

Answer 3

select boss_id, staff_name 
from staff 
group by boss_id, staff_name 
having count(staff_id)>=3