有几种方法，一种是使用not exists

select * 
from t
where department = 'it' 
and not exists (select * from t t2 where t2.id = t.id and t2.department != 'it');

另一种方法是使用聚合窗口函数：

select id, name, country, department from (
    select *, 
      Min(department) over(partition by id) mind, 
      Max(department) over(partition by id) maxd
    from t
)t
where mind = maxd and maxd = 'it';

您可以使用group by对相同名称进行分组，并将每个部门行与min和max进行比较，以检查它是否只有一个部门，如下所示。

select name, min(department) department
from   t
group by name
having min(department) = max(department)

您可以通过添加另一个条件（min用于部门，因为它在having语句中）来过滤IT部门，如下所示。

select name, min(department) department
from   t
group by name
having min(department) = max(department)
and min(department) = 'IT'

并且可以通过在子查询中使用它来获取所有未分组的行，如下所示。

select *
from   t
where  name in
(
    select name
    from   t
    group by name
    having min(department) = max(department)
    and min(department) = 'IT'
)

演示：https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=add8a5278770cf889a0e898fce6ad9a7

试试这个：

SELECT id, name, country, department 
FROM test
WHERE department='IT' AND name NOT IN (SELECT name FROM test WHERE 
department<>'IT')
ORDER BY id asc;

根据您的需要工作，在MySQL和SQL Server中测试。

你可以选择条件分组。

;with cte_EmployeeCounts AS
(
SELECT id, count(*) as Total_Dept, count(case when department = 'IT' THEN 1 END) as Total_IT_Dept
FROM TableName
GROUP BY id
)
SELECT * FROM TableName
where id in  (SELECT id from cte_EmployeeCounts WHERE Total_Dept = Total_IT_Dept)