sql - 如何在内部查询列中使用外部查询列

Question

我正在尝试在下面运行 postgresql 查询

SELECT *
FROM user_follow uf
       INNER JOIN (SELECT uplr.post, COUNT(*)
                   FROM user_post_like_relation uplr
                   WHERE uplr.category = 'like' AND uf.recently_viewed < uplr.created_at . //CANNOT reference uf
                     AND clicked = true
                   GROUP BY uplr.post) uplr
    ON uplr.post = uf.post
INNER JOIN post p ON uf.post = p.id

不幸的是，我无法在 INNER JOIN 查询中引用 uf 但我必须加入并在 JOIN 查询中引用 recently_viewed 列。有没有办法克服这个问题？

Answer 1

我认为在您的情况下，只需JOIN 到表并GROUP 结果以执行COUNT 就足够了。

SELECT uf.post, p.id, count(*)
FROM user_follow uf
JOIN post p ON uf.post = p.id
JOIN user_post_like_relation uplr ON uplr.post = uf.post
WHERE uplr.category = 'like'
  AND clicked = 'true'
  AND uf.recently_viewed < uplr.created_at
GROUP BY uf.post, p.id

使用 SELECT 和 GROUP BY 列来获取您需要的列。