您可以仅使用联接来包含在子查询中出现一次的id：

SELECT  id,
        label
  FROM  original_table ot
    INNER JOIN  (
                SELECT  id
                  FROM  original_table
                  GROUP BY id
                  HAVING COUNT(*) = 1
                ) a ON a.id = ot.id;

或者你可以用一个 IN 条款：

SELECT  id,
        label
  FROM  original_table
  WHERE id IN (SELECT   id
                  FROM  original_table
                  GROUP BY id
                  HAVING COUNT(*) = 1
              );

你可以试试这个：

SELECT t.id, t.label
FROM tbl AS t
JOIN (SELECT id FROM tbl GROUP BY id HAVING count(label) = 1) AS t1
ON t.id = t1.id;

假设两人 id 以及 label 是独一无二的，你可以用 NOT EXISTS 以及相关子查询。

SELECT t1.id,
       t1.label
       FROM original_table t1
       WHERE NOT EXISTS (SELECT *
                                FROM original_table t2
                                WHERE t2.id = t1.id
                                      AND t2.label <> t1.label);

是的，您的方法是正确的，您可能需要更改having count条件，并且在引用cte时，您可能需要稍微更改您的语法，但是您也可以在没有cte的情况下使用exists条件在同一行中完成。

Create table  temp  (ID int , Label varchar(10)); 

insert into  temp values 
(1  ,      "john" ), 
(1   ,     "henry" ) , 
(1   ,     "sara" )  , 
( 2   ,    "henry"  ) , 
(3   ,     "tim" ) ; 

select t.ID , t.Label from temp t 
where exists (
select ID, count(1) Dups  from temp t1 where t1.ID = t.ID group by ID having count(1) 
= 1)

输出：

ID, Label
    2, henry
    3, tim

我认为聚合是最简单的方法：

select id, min(label) as label
from original_table t
group by id
having count(*) = 1;