MySQL子查询

是的,有可能:

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

没有子查询变量更慢,0.0208秒,mysql在投票表中只使用271行,但当我使用连接时,它使用整行。这里没有子查询变量:

SELECT res.*, COUNT(*) FROM
(
  SELECT login, posts.post_n, SUM(vote)AS votes
  FROM users, posts, votes
  WHERE users.id=posts.id AND posts.post_n=votes.post_n AND visibility=3
  GROUP BY posts.post_n
)AS res, comments
WHERE comments.post_n=res.post_n
GROUP BY res.post_n
ORDER BY date DESC
LIMIT 0, 15