sql - 仅从主表中选择子表行满足条件的行

Question

在我的图像分类软件中有表result 和image。一个结果可以包含多张图片。在 image.preclassification

列中使用值“P”或 negative“N”可以将每个图像分类为 positive

一个结果，其中更多的图像是积极的。

我只想选择积极的结果。

在阅读了几个小时的 PostgreS 文档之后，我得出了这样一个让我害怕的解决方案:

WITH tmp AS (
SELECT result.result_id AS res, image.result_id , Count( image.preclassification ) AS ImgAll,  
SUM(
CASE image.preclassification 
  WHEN 'P' THEN 1
  ELSE 0
END
) AS ImgPos

from result, image
WHERE result.result_id = image.result_id
GROUP BY result.result_id, image.result_id
)
SELECT result_id
FROM tmp
WHERE ImgPos > ImgAll/2

我的问题是，是否有更简单的解决方案/方法来解决此类(ihmo 非常常见的)问题？

编辑:解释

首先，我创建了一个临时表，其中的列包含正面图像的计数并计算结果的所有图像。在下一步中，我只选择行，其中正图像的数量超过所有图像的一半。我的第一个想法是在第一个 WHERE 语句中使用 ImgPos > ImgAll/2 而不是使用 WITH 子句。但它不起作用，因为 ImgPos，ImgAll 被报告为未知列。

Answer 1

聪明的查询。但我认为你可以简化它:

select r.result_id
from result r join
     image i
     on r.result_id = i.result_id
group by r.result_id
having sum(case when i.preclassification = 'P' then 1 else 0 end) >
       sum(case when i.preclassification = 'N' then 1 else 0 end);

你也可以这样写:

select r.*
from (select r.result_id,
             sum(case when i.preclassification = 'P' then 1 else 0 end) as NumPos,
             sum(case when i.preclassification = 'N' then 1 else 0 end) as NumNeg
      from result r join
           image i
           on r.result_id = i.result_id
      group by r.result_id
     ) r
where NumPos > NumNeg;