您可以尝试使用计数窗口功能如下：

Select ID, Value
From
(
  Select ID, Value,
    Count(Case When Value = 'z' Then 1 End) Over (Partition By ID) As z_cnt, 
    Count(*) Over (Partition By ID) As all_cnt
  From table_name
) T
Where z_cnt <> all_cnt

参见demo。

可以使用 exists 半连接：

select *
    from t
    where not exists (
        select *
        from t t2
        where t2.Id = t.Id and t.Value = 'Z'
        group by id 
        having Count(distinct value) = 1
    );

您可以根据自己的条件使用EXCEPT

SELECT [ID], [Value] FROM tab
EXCEPT
SELECT [ID], [Value] FROm tab t1 WHERE  [Value] = 'z' AND (SELECT COUNT(*) FROm tab WHERE [ID] = t1.[ID]) = 1

| 识别号|价值|
| - ------|- ------|
| 1个|x|
| 1个|Y型|
| 1个|z|
| 第二章|x|
| 第二章|z|
fiddle
如果数据不是唯一的，则可以使用NOT IN代替
x一个一个一个一个x一个一个二个一个x一个一个三个一个
| 识别号|价值|
| - ------|- ------|
| 1个|x|
| 1个|Y型|
| 1个|z|
| 第二章|x|
| 第二章|z|
| 四个|x|
| 四个|x|
| 四个|x|
| 五个|Y型|
| 五个|Y型|
| 五个|Y型|
fiddle