group by？

Question

我有三个表，结构概述如下：

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    username    VARCHAR(255) UNIQUE
);
CREATE TABLE posts (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT REFERENCES users(id) NOT NULL,
    category    BIGINT REFERENCES categories(id) NOT NULL,
    text        TEXT NOT NULL
);
CREATE TABLE posts_votes (
    user_id     BIGINT REFERENCES users(id) NOT NULL,
    post_id     BIGINT REFERENCES posts(id) NOT NULL
    value       SMALLINT NOT NULL,
    PRIMARY KEY(user_id, post_id)
);

我能够使用下面的查询来组合一个查询，该查询获取每个帖子及其用户及其总价值：

SELECT p.id, p.text, u.username, COALESCE(SUM(v.value), 0) AS vote_value
FROM posts p
    LEFT JOIN posts_votes v ON p.id=t.post_id
    JOIN users u ON p.user_id=u.id
WHERE posts.category=1337
GROUP BY p.id, p.text, u.username

但现在我还想返回一个列，该列返回上述查询中每个post_id n的SELECT COALESCE((SELECT value FROM posts_votes WHERE user_id=1234 AND post_id=n), 0)结果。做这件事最好的方法是什么？

Answer 1

我认为额外的LEFT JOIN是一种合理的方法：

SELECT p.id, p.text, u.username, COALESCE(SUM(v.value), 0) AS vote_value,
       COALESCE(pv.value, 0)
FROM posts p JOIN
     users u
     ON p.user_id=u.id LEFT JOIN
     topics_votes v
     ON p.id = t.post_id LEFT JOIN
     post_votes pv
     ON pv.user_id = 1234 AND pv.post_id = p.id
WHERE p.category = 1337
GROUP BY p.id, p.text, u.username, pv.value;