获取相同状态的结果集的SQL查询

Question

表1如下，

<表类> ID FK1_ID FK2_ID tbody><<tr>111212313

看起来您只需要将Status的条件计数与完整计数进行比较，并将CASE用于最终结果。

SELECT
t1.FK1_ID,
Result = CASE WHEN COUNT(*) = COUNT(CASE WHEN FK2.Status = 'True' THEN 1 END)
THEN 'Yes'
ELSE 'No' END
FROM table1 t1
JOIN FK2 ON FK2.ID = t1.FK2_ID
AND FK2.Type = 'Type1'
GROUP BY
t1.FK1_ID;

一个略短但不太容易理解的版本

CASE WHEN COUNT(*) = COUNT(NULLIF(FK2.Status, 'False'))

或者

CASE WHEN COUNT(NULLIF(FK2.Status, 'True')) = 0

我不完全遵循你的逻辑(这两个表是如何连接的)，但听起来你想比较一个总计数与一个条件计数，所以也许像

with t as (select type, count(status) as cnt,
sum(case when status ='True' then 1 else 0 end) as truecnt
from FK2
group by type)
select type, case when truecnt > 0 and cnt = truecnt then 'Yes' else 'No' end as MyResult
from t