gpt4 book ai didi

MySQL 子查询

转载 作者:行者123 更新时间:2023-11-29 07:13:52 25 4
gpt4 key购买 nike

我们可以在没有子查询的情况下执行此查询吗?

SELECT login, post_n,

(SELECT SUM(vote) FROM votes WHERE votes.post_n=posts.post_n)AS votes,
(SELECT COUNT(comments.post_n) FROM comments WHERE comments.post_n=posts.post_n)AS comments_count

FROM users, posts
WHERE posts.id=users.id AND (visibility=2 OR visibility=3)
ORDER BY date DESC LIMIT 0, 15

表格:

用户:id,登录名

帖子:post_n、id、可见性

投票:post_n,投票

id — 它是用户 ID,Users 是主表。

最佳答案

是的,这是可能的:

SELECT login, post_n,

SUM(vote) as votes,

FROM users
JOIN posts using(id)
LEFT JOIN votes using(post_n)
WHERE visibility=2 OR visibility=3
GROUP BY login, post_n

然后压平结果:

select * from 
(
SELECT login, post_n,

SUM(vote) as votes,

FROM users
LEFT JOIN posts using(id)
LEFT JOIN votes using(post_n)
WHERE visibility=2 OR visibility=3
GROUP BY login, post_n
) as votes_count

然后加入评论:

select votes_count.login, votes_count.post_n, votes_count.votes, 

COUNT(comments.post_n) as comments_count

from
(
SELECT login, post_n,

SUM(vote) as votes,

FROM users
LEFT JOIN posts using(id)
LEFT JOIN votes using(post_n)
WHERE visibility=2 OR visibility=3
GROUP BY login, post_n
) as votes_count
LEFT JOIN comments using(post_n)
GROUP BY votes_count.login, votes_count.post_n
ORDER BY date DESC LIMIT 0, 15

关于MySQL 子查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/2788621/

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