如何用子查询替代join select a.*,b.*,c.* from a left join b on a.id=b.a_id left join c on b.id = c.b_id

你可以使用以下的子查询语句来代替该JOIN查询：

SELECT a.*,
(SELECT * FROM b WHERE b.a_id = a.id) AS b,
(SELECT * FROM c WHERE c.b_id in (SELECT id FROM b WHERE b.a_id = a.id)) AS c
FROM a

相关问题

想要删除结果select a.* from a left JOIN b ON a.id=b.id where b.id is null 中的数据，语句

你可以使用DELETE语句结合子查询来删除这些数据，具体语句如下：

DELETE FROM a WHERE id IN (SELECT a.id FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL);

这条语句中，首先使用子查询找出符合条件的id，然后将这些id对应的数据从表a中删除。注意，这里使用了LEFT JOIN和WHERE b.id IS NULL来找出表b中不存在的数据，也可以使用NOT EXISTS来实现相同的效果。

FROM eci_erp_shipment_info_domestic_trade a LEFT JOIN ( SELECT a.* FROM eci_app_shipment_info_domestic_trade a,( SELECT max(apply_time) apply_time ,dn1 FROM eci_app_shipment_info_domestic_trade GROUP BY dn1 ) b WHERE a.dn1=b.dn1 AND a.apply_time=b.apply_time ) b on a.dn1=b.dn1 LEFT JOIN ( SELECT a.* FROM eci_app_shipment_info_domestic_trade_alter a,( SELECT max(apply_time) apply_time ,dn1 FROM eci_app_shipment_info_domestic_trade_alter GROUP BY dn1 ) b WHERE a.dn1=b.dn1 AND a.apply_time=b.apply_time ) c ON a.dn1 = c.dn1 LEFT JOIN eci_reply_shipment_info_domestic_trade d ON d.apply_id=b.id WHERE (a.app_status=6 OR a.app_status=9) and a.del_flag='0' 优化一下

您可以尝试优化以下SQL查询语句：

SELECT a.*
FROM eci_erp_shipment_info_domestic_trade a
LEFT JOIN (
    SELECT a.*
    FROM eci_app_shipment_info_domestic_trade a
    INNER JOIN (
        SELECT MAX(apply_time) AS apply_time, dn1
        FROM eci_app_shipment_info_domestic_trade
        GROUP BY dn1
    ) b ON a.dn1 = b.dn1 AND a.apply_time = b.apply_time
) b ON a.dn1 = b.dn1
LEFT JOIN (
    SELECT a.*
    FROM eci_app_shipment_info_domestic_trade_alter a
    INNER JOIN (
        SELECT MAX(apply_time) AS apply_time, dn1
        FROM eci_app_shipment_info_domestic_trade_alter
        GROUP BY dn1
    ) b ON a.dn1 = b.dn1 AND a.apply_time = b.apply_time
) c ON a.dn1 = c.dn1
LEFT JOIN eci_reply_shipment_info_domestic_trade d ON d.apply_id = b.id
WHERE (a.app_status = 6 OR a.app_status = 9) AND a.del_flag = '0';

该优化主要是对子查询进行了优化，使用INNER JOIN替代了LEFT JOIN，并将子查询中的GROUP BY语句移至子查询中进行聚合计算，减少了不必要的数据量和计算，提高了查询效率。同时，还对表别名进行了调整，使得查询更加清晰易读。请根据实际情况测试并验证该优化是否符合您的需求。