select 姓名,count(部门) from 表
where count(部门)>1
group by 姓名
SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 城市 ASC) [I],* FROM table
) A WHERE A.I=2
select name from a group by name having count(city) = 2
SELECT 姓名,count(部门) num, FROM 表
GROUP BY 姓名
) a WHERE num=2