sql语句优化

1、批量更新数据

UPDATE s_payablebill SP 
SET SP.AdjustAmount = (SELECT SUM(SA.Amount) FROM s_payablebill_adjust SA WHERE SA.PayableBillId = SP.PayableBillId) 
WHERE
    SP.PayableBillId IN (SELECT PayableBillId FROM s_payablebill_adjust GROUP BY PayableBillId)

=》

UPDATE s_payablebill SP 
INNER JOIN (SELECT PayableBillId, SUM(Amount) AdjustAmount FROM s_payablebill_adjust GROUP BY PayableBillId) SA ON SP.PayableBillId = SA.PayableBillId
SET SP.AdjustAmount = SA.AdjustAmount

 2、逗号拼接

SELECT Group_concat(T.GuideName SEPARATOR ',') FROM (
SELECT
    TS.TourId,
    TG.GuideName
FROM
    t_tourdatesegment TS
    LEFT JOIN t_tourdatesegment_guide TG ON TS.TourDateSegmentId = TG.TourDateSegmentId
WHERE
    TS.State = 1 AND 
    TS.TourId = 1806281010 AND
    TG.State > -1
GROUP BY TG.GuideName) T

0.037s =》0.035s

SELECT
    Group_concat(DISTINCT TG.GuideName)
FROM
    t_tourdatesegment_guide TG 
WHERE
    TG.TourId = 1806281010 
    AND TG.State > - 1 
    AND EXISTS ( SELECT 1 FROM t_tourdatesegment TS WHERE TS.TourDateSegmentId = TG.TourDateSegmentId AND TS.State = 1 )