原join 连接语句
SELECT
SUM(video_flowers.number) AS num,
video_flowers.flower_id,
flowers.title,
flowers.image
FROM
`video_flowers`
JOIN
`flowers` ON `video_flowers`.`flower_id` = `flowers`.`id`
JOIN
`video_posts` ON `video_flowers`.`video_post_id` = `video_posts`.`id`
WHERE
`video_posts`.`user_id` = 36
GROUP BY `video_flowers`.`flower_id`
可以优化成
SELECT
vf.num, flowers.title, flowers.image
FROM
`flowers`
<span class="hljs-keyword">join</span></br>
(<span class="hljs-keyword">SELECT</span> </br>
<span class="hljs-keyword">SUM</span>(video_flowers.<span class="hljs-built_in">number</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">num</span>, video_flowers.flower_id, video_flowers.video_post_id
<span class="hljs-keyword">FROM</span></br>
video_flowers </br>
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> <span class="hljs-string">`video_flowers`</span>.<span class="hljs-string">`flower_id`</span>) <span class="hljs-keyword">AS</span> vf <span class="hljs-keyword">ON</span> <span class="hljs-string">`vf`</span>.<span class="hljs-string">`flower_id`</span> = <span class="hljs-string">`flowers`</span>.<span class="hljs-string">`id`</span></br>
<span class="hljs-keyword">join</span> <span class="hljs-string">`video_posts`</span> <span class="hljs-keyword">on</span> <span class="hljs-string">`video_posts`</span>.<span class="hljs-string">`id`</span> = vf.<span class="hljs-string">`video_post_id`</span></br>
<span class="hljs-keyword">where</span> video_posts.user_id = <span class="hljs-number">36</span>;</code></pre>
这样就没有using filesort 和using temporary
