select name, id from mytable where id not in
(select distinct id from mytable where state <> 4)

您可以这样使用“不存在”：

select distinct name, id 
from table1 a
where not exists (select *
                  from table1 b
                  where  a.id=b.id and state<>4)

您可以使用聚合，如下所示：

SELECT name, id
FROM your_table
GROUP BY name, id
HAVING SUM(state<>4)=0;

查看SQLFiddle的演示。

您可能需要2个子查询。
选择按名称分组的状态4
按名称分组选择
比较计数如果计数相同，则选择它
示例：select name，count（名称）from table where state=4 as t1 select name，count（名称）from table as t2 select t1.name from t1和t2 where t2.count=t1.count

在更一般的情况下，可以使用count distinct（with not exists或with a join）：

select distinct name, id 
from table1 a
where not exists (
select *
from table1 b
where  a.id=b.id
group by id
HAVING count(distinct state)>1)