SQL Server如何排除含指定Service的ID？

原查询的问题在于仅排除了service = 'medical'的单行记录，但那些包含medical服务的ID的其他服务行依然会被返回。要实现"只要ID存在medical服务，就排除该ID所有行"的需求，可以用以下几种方式：

方法1：使用NOT EXISTS子查询 #

这是最直观的方式，直接筛选出不存在任何medical服务记录的ID对应的所有行：

SELECT 
    AQ.id,
    it.service 
FROM [system].[dbo].[Data] AQ
INNER JOIN [system].[dbo].[data_Items] it ON it.id = AQ.id
WHERE NOT EXISTS (
    SELECT 1 
    FROM [system].[dbo].[data_Items] it_sub 
    WHERE it_sub.id = AQ.id 
      AND it_sub.service = 'medical'
)

解释：子查询会检查当前ID是否存在medical服务，若存在则外层查询直接排除该ID的所有行。

方法2：使用LEFT JOIN + IS NULL #

通过左连接到包含medical服务的ID集合，筛选出匹配不到的ID（即无medical服务的ID）：

SELECT 
    AQ.id,
    it.service 
FROM [system].[dbo].[Data] AQ
INNER JOIN [system].[dbo].[data_Items] it ON it.id = AQ.id
LEFT JOIN (
    SELECT DISTINCT id 
    FROM [system].[dbo].[data_Items] 
    WHERE service = 'medical'
) med_ids ON med_ids.id = AQ.id
WHERE med_ids.id IS NULL

解释：先提取所有有medical服务的ID，再左连接到主查询，筛选出左连接后med_ids.id为NULL的记录——这些就是无medical服务的ID的所有行。

方法3：使用GROUP BY + HAVING（先过滤ID再关联） #

先分组筛选出无medical服务的ID，再关联回原表获取这些ID的所有服务行：

WITH valid_ids AS (
    SELECT id
    FROM [system].[dbo].[data_Items]
    GROUP BY id
    HAVING SUM(CASE WHEN service = 'medical' THEN 1 ELSE 0 END) = 0
)
SELECT 
    AQ.id,
    it.service 
FROM [system].[dbo].[Data] AQ
INNER JOIN [system].[dbo].[data_Items] it ON it.id = AQ.id
INNER JOIN valid_ids ON valid_ids.id = AQ.id

解释：CTE里通过分组统计每个ID的medical服务数量，数量为0的就是符合要求的ID，再关联回主表获取这些ID的所有服务行。

另外，原查询中的DISTINCT可以去掉（如果数据本身存在重复的id+service组合，可保留）。

内容的提问来源于stack exchange，提问作者Anaa