mysql - mysql中子查询join语句的高效写法

Question

我有一个 reviews 表，如下所示:

用户可以对这些评论投赞成票或反对票。为此，我正在维护另一个名为 review_counts 的表。它看起来像下面的那个:

这里，1表示赞成票，-1表示反对票。

现在，我将加入这两个表，这样我将获得包含赞成票总数和反对票总数的评论。为实现这一点，我编写了以下运行良好的查询。

 SELECT * FROM `reviews` as x 
 LEFT JOIN 
 (SELECT count(votes) as vote_up, review_id FROM `review_counts` WHERE votes = 1) as y ON x.review_id = y.review_id
 LEFT JOIN
 (SELECT count(votes) as vote_down, review_id FROM `review_counts` WHERE votes = -1) as z ON x.review_id = z.review_id

为此，我得到这样的结果:

现在，问题是我在同一张表上使用两个 JOINS 来获得赞成票和反对票，是否有任何其他方法可以使用单个 join 语句获得类似的结果？

Answer 1

您可以使用单个 LEFT JOIN 和 SUM(CASE WHEN...END) 来完成此操作:

CREATE TABLE reviews(
    id          INT,
    review_id   VARCHAR(10),
    review      VARCHAR(10)
)
CREATE TABLE review_counts(
    id          INT,
    review_id   VARCHAR(10),
    votes       INT
)
INSERT INTO reviews VALUES
(1, 'review1', 'Review 1'),
(2, 'review2', 'Review 2');
INSERT INTO review_counts VALUES
(1, 'Review1', 1),
(2, 'Review1', 1),
(3, 'Review1', 1),
(4, 'Review1', 1),
(5, 'Review1', 1),
(6, 'Review2', -1),
(7, 'Review2', -1),
(8, 'Review2', -1),
(9, 'Review2', -1),
(10, 'Review2', -1);

SELECT
    r.*,
    SUM(CASE WHEN c.votes = 1 THEN 1 ELSE 0 END) AS Vote_Up,
    SUM(CASE WHEN c.votes = -1 THEN 1 ELSE 0 END) AS Vote_Down
FROM reviews r
LEFT JOIN review_counts c
    ON c.review_id = r.review_id
GROUP BY r.id, r.review_id, r.review

DROP TABLE reviews
DROP TABLE review_counts

结果

id          review_id  review     Vote_Up     Vote_Down
----------- ---------- ---------- ----------- -----------
1           review1    Review 1   5           0
2           review2    Review 2   0           5