mysql可以不用子查询吗,MySQL-我可以避免这些相关/相关子查询吗？

I have a MySQL query that I have been optimising, and currently it has 2 dependent / correlated subqueries.

I was wondering if it was possible to re write to avoid these?

SELECT *

FROM `pp_slides`

JOIN `pp_slide_content`

ON `pp_slides`.`id` = `pp_slide_content`.`slide_id`

AND `pp_slide_content`.`version` = (

SELECT max(`version`) FROM `pp_slide_content` WHERE `slide_id` = `pp_slides`.`id`

)

LEFT JOIN `pp_published_slides`

ON `pp_published_slides`.`slide_id` = `pp_slides`.`id`

AND `pp_published_slides`.`slide_version` = `pp_slide_content`.`version`

AND `pp_published_slides`.`publish_id` = (

SELECT max(`publish_id`) FROM `pp_published_slides` WHERE `pp_published_slides`.`slide_id` = `pp_slides`.`id` AND `pp_published_slides`.`slide_version` = `pp_slide_content`.`version`

)

LEFT JOIN `pp_publish` ON `pp_publish`.`id` = `publish_id`

WHERE `pp_slides`.`product_id` = '2'

AND `pp_slides`.`country_code` = 'gb'

A quick overview:

A slide is created, and supports versioned changes.

A slide (and other entities) are then published.

The slide and the version that is published is set in the pp_published_slides tables.

And the overall publish object is saved in pp_publish.

The above SQL will load up a slide object, and include extra data about the latest version, when it was published etc.

Any help would be greatly appreciated, kinda at the limits of my SQL knowledge....

解决方案

Here's an example showing part of your query rewritten without the correlated subquery...

SELECT s.*

, c.*

FROM slides s

JOIN slide_content c

ON c.slide_id = s.id

JOIN ( SELECT slide_id, MAX(version) max_version FROM slide_content GROUP BY slide_id ) x

ON x.slide_id = c.slide_id

AND x.max_version = c.version

WHERE s.product_id = 2

AND s.country_code = 'gb';