在PostgreSQL查询中两次对同一个表进行内部联接

Question

我正在努力处理PostgreSQL数据库上的以下查询，该数据库最初是用Doctrine query Builder构建的(但这与此处无关(。

我有一个名为"；课程"；其在"；日志"；桌子

log.resumeComplete的记录有一个日期，log.activated的记录有日期。

在这一个查询中，我希望提取具有比X早的log.activated日期和比Y早的log.resumeComplete日期的记录；课程"；记录在Log表中没有相应的log.resumeComplete记录。

有没有一种方法可以通过一个查询获得这些信息？

SELECT s0_.slug AS slug, s0_.status AS status, a2_.time AS resumeTime
FROM Course s0_ 
INNER JOIN Log a1_ ON (a1_.course_id = s0_.id) 
INNER JOIN Log a2_ ON (s0_.id = a2_.course_id AND a2_.type = 'log.resumeComplete')
WHERE s0_.status = 'active' 
AND s0_.currentsss = '72' 
AND a1_.type = 'log.activated'
AND a1_.time < '2020-05-31 14:25:31'   
AND a2_.time IS NULL OR a2_.time < '2020-07-11 14:25:31'
ORDER BY s0_.id ASC;

Answer 1

这个查询应该工作(没有小提琴/测试集(：

更改：

• 内部联接=>左联接
• 首次加入条件上移
• "或"；在所有透水条件下工作，使其变得无用"(("；必需
• 可能是隐式类型转换(time<string(，不利于索引。没有纠正，没有小提琴

SQL

SELECT s0_.slug AS slug, s0_.status AS status, a2_.time AS resumeTime
FROM Course s0_ 
INNER JOIN Log a1_ ON (a1_.course_id = s0_.id and AND a1_.type = 'log.activated')  
left JOIN Log a2_ ON (a2_.course_id = s0_.id AND a2_.type = 'log.resumeComplete')
WHERE s0_.status = 'active' 
AND s0_.currentsss = '72' 

AND a1_.time < '2020-05-31 14:25:31'   
AND (a2_.time IS NULL OR a2_.time < '2020-07-11 14:25:31') -- this type of comparison might cause an implicet conversion of a2_.time to varchar. bad
ORDER BY s0_.id ASC;