在嵌套查询中，检查条件的所有值

Question

如何摆脱嵌套查询（同意，不同意）？如何重写加入 - 我想不出。也许还有其他最佳解决方案？

select *
from (
    select
        (select count(id) from Agreement a where a.ConclusionCardFile = f.id and a.Status = 1) agreed,
        (select count(id) from Agreement a where a.ConclusionCardFile = f.id and (a.Status <> 1 or a.Status is null)) dis_agreed
    from ConclusionCard_Files f
) t
where t.agreed > 0 and dis_agreed = 0

Answer 1

您可以将条件编写为 where 子句：

select *
from conclusionCard_Files
where exists (
    select *
    from agreement
    where agreement.conclusionCardFile = conclusionCard_Files.id
    having sum(case when status = 1 then 1 else 0 end) > 0
    and    sum(case when status = 1 then 0 else 1 end) = 0
)

Answer 2

也许您只是使用子查询来过滤？将它们移至 WHERE 子句怎么样？

SELECT 
   *
FROM ConclusionCard_Files f
WHERE 
    EXISTS(select * from  Agreement a where (a.ConclusionCardFile = f.id) and a.Status =1)
    AND NOT EXISTS(select * from  Agreement a where (a.ConclusionCardFile = f.id) and (a.Status != 1 or a.Status is null))  

它对性能友好，因为 SqlServer 不会计算所有计数

Answer 3

如果我理解正确，您可以尝试将 JOIN 与 HAVING 条件聚合函数一起使用。

SELECT COUNT(CASE WHEN a.Status = 1 THEN ID END) agreed,
       COUNT(CASE WHEN a.Status <> 1 or a.Status is null THEN ID END) dis_agreed
FROM Agreement a 
INNER JOIN ConclusionCard_Files f
ON a.ConclusionCardFile = f.id
HAVING 
    COUNT(CASE WHEN a.Status = 1 THEN ID END) > 0
AND 
   COUNT(CASE WHEN a.Status <> 1 or a.Status is null THEN ID END) = 0

请编辑

如果您想根据您的情况从 ConclusionCard_Files 获取数据， 。您可以尝试让子查询中的条件聚合函数来自表Agreement的每个ConclusionCardFile，然后执行JOIN

SELECT f.*
FROM (
    SELECT COUNT(CASE WHEN a.Status = 1 THEN ID END) agreed,
           COUNT(CASE WHEN a.Status <> 1 or a.Status is null THEN ID END) dis_agreed,
           a.ConclusionCardFile
    FROM Agreement a 
    GROUP BY a.ConclusionCardFile
) a 
INNER JOIN ConclusionCard_Files f
ON a.ConclusionCardFile = f.id
WHERE a.agreed > 0 AND a.dis_agreed = 0