前置子查询如何优化成join

Question

SELECT
    IfNull( f.JOIN, 0 ) AS `join`,
    IfNull( t.people_num, 0 ) AS need,
    IfNull( f.found_id, 0 ) AS found_id,
    t.team_id as team_id,
    t.user_notpz_reward AS rebate_money,
    t.del,
    g.id,
    g.NAME,
    g.image,
    g.min_price AS price,
    g.market_price,
    
     (select tg.team_price from ls_team_goods_item tg where g.id = tg.item_id and t.team_id = tg.team_id)  jiage
FROM
    `ls_goods` `g`
    
    LEFT JOIN `ls_team_activity` `t` ON `g`.`id` = `t`.`goods_id`

    LEFT JOIN ( SELECT id AS found_id, need, team_id, max( `join` ) AS `join` FROM ls_team_found WHERE STATUS = 0 GROUP BY team_id ) f ON `f`.`team_id` = t.team_id 
    
WHERE
    `g`.`del` = 0 
    AND `g`.`status` = 1 
    AND `g`.`is_best` = 1 
    AND `g`.`is_team` = 1
    AND t.del = 0
    and t.status = 1
ORDER BY
    `g`.`sort` DESC,
    `g`.`id` DESC 
    LIMIT 0,
    15

 (select tg.team_price from ls_team_goods_item tg where g.id = tg.item_id ，商品表id=规格id and t.team_id = tg.team_id)  jiage ，这个是查询实际拼团的价格，根据商品表id查出的。g.id = tg.item_id ，商品表id=规格id
 
 怎么把这个优化，变成join形式。

Answer 1

你直接直接使用expain分析sql

Answer 2

SELECT

IfNull( f.JOIN, 0 ) AS `join`,
IfNull( t.people_num, 0 ) AS need,
IfNull( f.found_id, 0 ) AS found_id,
t.team_id as team_id,
t.user_notpz_reward AS rebate_money,
t.del,
g.id,
g.NAME,
g.image,
g.min_price AS price,
g.market_price,
tg.team_price AS jiage

FROM

`ls_goods` `g`

LEFT JOIN `ls_team_activity` `t` ON `g`.`id` = `t`.`goods_id`
    
    LEFT JOIN ls_team_goods_item tg ON g.id = tg.item_id and t.team_id = tg.team_id

LEFT JOIN ( SELECT id AS found_id, need, team_id, max( `join` ) AS `join` FROM ls_team_found WHERE STATUS = 0 GROUP BY team_id ) f ON `f`.`team_id` = t.team_id 

WHERE

`g`.`del` = 0 
AND `g`.`status` = 1 
AND `g`.`is_best` = 1 
AND `g`.`is_team` = 1
AND t.del = 0
and t.status = 1

ORDER BY

`g`.`sort` DESC,
`g`.`id` DESC 
LIMIT 0,
15