求一SQL优化解决思路

求一SQL优化

SQL code

SELECT      mp.id,     mp.phone,     mp.carrier_id,     mp.region_id,     rg.name AS regionname,     ca.name AS carriername,     rg.area_identity AS areaidentity FROM     gsms_region_carrier_map AS mp      JOIN gsms_carrier AS ca           ON mp.carrier_id = ca.id      JOIN gsms_region_code AS rg           ON mp.region_id = rg.id WHERE mp.is_removed = FALSE      AND mp.id NOT IN      (SELECT           mp2.remove_id      FROM          gsms_region_carrier_map AS mp2      WHERE is_removed = TRUE)

我想把后面的那个子查询变成 join 形式的.
ps:gsms_region_carrier_map 表id是主键.

[解决办法]
SELECT 
mp.id,
mp.phone,
mp.carrier_id,
mp.region_id,
rg.name AS regionname,
ca.name AS carriername,
rg.area_identity AS areaidentity 
FROM
gsms_region_carrier_map AS mp 
JOIN gsms_carrier AS ca 
ON mp.carrier_id = ca.id 
JOIN gsms_region_code AS rg 
ON mp.region_id = rg.id 
left join gsms_region_carrier_map mp2
on mp.id =mp2.remove_id 
WHERE mp.is_removed = FALSE 
AND mp2.remove_id is null

查看更多 下一篇