27,579
社区成员
发帖
与我相关
我的任务
分享
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