27,582
社区成员




select deptname,count(deptname)
from
(select e.id, e.name, d.name as deptname, d.deptid
from emp as e, dept as d
where e.deptid = d.deptid
)
as new
where deptid =
(select deptid from emp
group by deptid
having count(*) > 2)
group by deptname
;WITH emp(id,name,deptid)AS(
SELECT 1,'Leo','1' UNION ALL
SELECT 2,'Rick','4' UNION ALL
SELECT 3,'Amanda','3' UNION ALL
SELECT 4,'Lvy','3' UNION ALL
SELECT 5,'Tracy','4' UNION ALL
SELECT 6,'Jim','4' UNION ALL
SELECT 7,'Tony',NULL
),dept(deptid,name)AS(
SELECT 1,N'IT部' UNION ALL
SELECT 2,N'产品部' UNION ALL
SELECT 3,N'开发部' UNION ALL
SELECT 4,N'采购部'
)
SELECT * FROM (
SELECT emp.*,dept.name AS DeptName,COUNT(0)OVER(PARTITION BY dept.deptid) AS cnt FROM emp INNER JOIN dept ON emp.deptid=dept.deptid
) AS t WHERE t.cnt>2
id name deptid DeptName cnt
----------- ------ ------ -------- -----------
5 Tracy 4 采购部 3
6 Jim 4 采购部 3
2 Rick 4 采购部 3