我有两个表 Family：

family_Name | family_id

和 Types：

family_fk | type

请求#1： 我想在只有一个类型为 'multiple' 时获取所有的 family_id。

请求#2： 我想在存在多于一个类型且其中包含一个type= 'multiple' 时获取所有的 family_id。

对于第一项任务，我写了这条语句，但 COUNT(*) 是错的——它返回具有type = 'multiple' 的所有族的总数：

SELECT cv.family_id, COUNT(*)
FROM familys f, types it 
WHERE f.family_id = it.family_fk 
  AND it.type = 'multiple'
GROUP BY cv.family_id
HAVING COUNT(*) = 1;

解决方案

对于 "familys" 行，只有一个类型且这个类型是 'multiple'：

SELECT f.family_id, COUNT(*)
FROM familys f
INNER JOIN types it ON f.family_id = it.family_fk 
GROUP BY f.family_id
HAVING max(type)=min(type) and min(type)='multiple';

对于 "familys" 行，拥有多于一个类型且其中一个是 'multiple'：

SELECT f.family_id, COUNT(*),
  sum(CASE WHEN it.type='multiple' then 1 else 0 end) count_multiple
FROM familys f
INNER JOIN types it ON f.family_id = it.family_fk 
GROUP BY f.family_id
HAVING COUNT(*)> 1 
   AND count_multiple>0 
   -- or instead of, for older than Oracle 26
   -- AND sum(CASE WHEN it.type='multiple' then 1 else 0 end)>0 
;

我建议使用JOIN而不是笛卡尔乘积。它更可靠，也更高效。“很久没有人这么做过了 :)”。

我们也可以用EXISTS和 NOT EXISTS以“经典”风格来编写这些查询：

SELECT f.family_id
FROM familys f
WHERE 
  EXISTS (
   SELECT 1 FROM types it 
   WHERE it.family_fk=f.family_id and type='multiple' )
 AND NOT EXISTS(
   SELECT 1 FROM types it 
   WHERE it.family_fk=f.family_id and type<>'multiple' )
;

和

SELECT f.family_id
FROM familys f
WHERE 
  EXISTS (
   SELECT 1 FROM types it 
   WHERE it.family_fk=f.family_id and type='multiple' )
 AND EXISTS(
   SELECT 1 FROM types it 
   WHERE it.family_fk=f.family_id and type<>'multiple' )
;

Fiddle