我有一个查询给我上面的错误。我的代码如下：

SELECT *,
      dense_rank() OVER (PARTITION BY email
                         ORDER BY priority_score,
                                  comp) AS r
FROM main_query
WHERE NOT EXISTS
   (SELECT name,
           event,
           email,
           report_date
    FROM gm
    WHERE  gm.name= main_query.name
      AND gm.event= main_query.event
      AND gm.email = main_query.email
      AND gm.report_date >= (CURRENT_DATE - 25)::date)
ORDER BY priority_score ASC

我看到的克服这些类型错误的一种解决方案是能够转换不相关查询中的相关子查询（sherlock）。因此，我正在寻找其他使用方法where not exists语句但没有相关子查询，即调用main_query子查询内的表 ((...) from gm left join main_query on(...))。有谁知道这是否可能以及如何做到？

非常欢迎任何建议，并提前非常感谢！

Does a LEFT JOIN版本工作？

SELECT mq.*,
      dense_rank() OVER (PARTITION BY mq.email
                         ORDER BY mq.priority_score, comp
                        ) AS r
FROM main_query mq LEFT JOIN
     gm
     ON gm.name = mq.name AND
        gm.event= mq.event AND
        gm.email = mq.email AND
        gm.report_date >= (CURRENT_DATE - 25)::date)
WHERE gm.name IS NULL  
ORDER BY priority_score ASC;

如果这不起作用，它应该像这样工作：

SELECT mq.*,
      dense_rank() OVER (PARTITION BY mq.email
                         ORDER BY mq.priority_score, comp
                        ) AS r
FROM main_query mq LEFT JOIN
     (SELECT gm.*
      FROM gm
      WHERE gm.report_date >= (CURRENT_DATE - 25)::date)
     ) gm
     ON gm.name = mq.name AND
        gm.event= mq.event AND
        gm.email = mq.email
WHERE gm.name IS NULL  
ORDER BY priority_score ASC