gpt4 book ai didi

mysql - 如何在不重复子查询的情况下选择所有这些数据?

转载 作者:行者123 更新时间:2023-11-29 02:04:06 25 4
gpt4 key购买 nike

假设我有一个名为“users”的表、一个名为“posts”的表和一个名为“ratings”的表,用于存储每个用户对每个帖子的评分。如果我想选择帖子,连同他们的赞、不赞和“平均”评分(四舍五入到小数点后第二位),我会这样做:

SELECT *,
(SELECT COUNT(*) FROM ratings WHERE thumb='up' AND post_id = posts.id) AS thumbs_up,
(SELECT COUNT(*) FROM ratings WHERE thumb='down' AND post_id = posts.id) AS thumbs_down,
ROUND((SELECT COUNT(*) FROM ratings WHERE thumb='up' AND post_id = posts.id) / (SELECT COUNT(*) FROM ratings WHERE post_id = posts.id), 2) AS average_rating
FROM posts;

但是有没有办法在不重复子查询的情况下获得相同的数据呢?我的意思是,理想情况下我想这样做:

SELECT *,
(SELECT COUNT(*) FROM ratings WHERE thumb='up' AND post_id = posts.id) AS thumbs_up,
(SELECT COUNT(*) FROM ratings WHERE thumb='down' AND post_id = posts.id) AS thumbs_down,
ROUND(thumbs_up / (thumbs_up + thumbs_down), 2) AS average_rating
FROM posts;

但是MySQL不允许这样。下一个最好的事情是什么?还是有更好的方法,使用 JOIN 或 UNION?

最佳答案

SELECT
*,
ROUND(thumbs_up / (thumbs_up + thumbs_down), 2) AS average_rating
FROM (
SELECT
YourColumns...
SUM(CASE WHEN r.thumb = 'up' THEN 1 END) AS thumbs_up,
SUM(CASE WHEN r.thumb = 'down' THEN 1 END) AS thumbs_down
FROM
posts p LEFT JOIN
ratings r ON r.post_id = p.id
GROUP BY YoursColumns
) sub

关于mysql - 如何在不重复子查询的情况下选择所有这些数据?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9540130/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com