计算有多少个ID(FK),其中ID(FK)相同,但列值相同!=给定值

一种方法是聚合:

select class
from trips t
group by class
having sum(case when trip = 'Observatory' then 1 else 0 end) = 0;

如果您有一个类表(可能是这样),请使用 not exists :

select c.*
from classes c
where not exists (select 1
                  from trips t
                  where t.classid = c.classid and
                        t.trip = 'Observatory'
                 );

计算班级数,计算去天文台的班级数,减去:

WITH cteTotal_class_count AS (SELECT COUNT(*) AS TOTAL_CLASS_COUNT
                                FROM CLASSES),
     cteObservatory_class_count AS (SELECT COUNT(DISTINCT CLASS_ID) AS OBSERVATORY_CLASS_COUNT
                                      FROM TRIPS
                                      WHERE UPPER(TRIP) = 'OBSERVATORY')
SELECT TOTAL_CLASS_COUNT - OBSERVATORY_CLASS_COUNT AS COUNT_OF_CLASSES_WHO_DIDNT_GO
  FROM cteTotal_class_count
  CROSS JOIN cteObservatory_class_count

dbfiddle here

与前面的答案相同,但有点简化:

SELECT (SELECT count(distinct class_id) FROM trips) - 
       (SELECT count(distinct class_id) FROM trips WHERE trip = 'Observatory');