select a.id,SUM(CASE WHEN d.shoukuan_month = '2023-05' and d.is_del = 0 then d.netincome ELSE 0 end) as one_shoukuan,SUM(CASE WHEN d.shoukuan_month = '2023-04' AND d.is_del = 0 THEN d.netincome ELSE 0 END) as two_shoukuan,(CASE WHEN b.month = '2023-05' THEN b.create_income ELSE 30000 END) as one_income,(CASE WHEN b.month = '2023-04' THEN b.create_income ELSE 30000 END) as two_income from a left join b on b.kefuid = a.id left join d on d.kefuid = a.id where a.brand = '1' and a.is_del = 0 and a.is_lizhi = 0 group by a.id这个sql如何让select 中的d.one_shoukuan >= b.one_income and d.two_shoukuan >= b.two_income这个多个join会导致sum多次循环导致sum不断叠加

你可以在外层使用HAVING子句来过滤结果集，而不是在WHERE子句中使用条件。因为HAVING子句是在分组完成后对聚合结果进行筛选的。这样可以避免SUM函数多次循环导致的重复计算。修改后的SQL如下：

SELECT 
    a.id,
    SUM(CASE WHEN d.shoukuan_month = '2023-05' and d.is_del = 0 THEN d.netincome ELSE 0 END) as one_shoukuan,
    SUM(CASE WHEN d.shoukuan_month = '2023-04' AND d.is_del = 0 THEN d.netincome ELSE 0 END) as two_shoukuan,
    (CASE WHEN b.month = '2023-05' THEN b.create_income ELSE 30000 END) as one_income,
    (CASE WHEN b.month = '2023-04' THEN b.create_income ELSE 30000 END) as two_income 
FROM a 
LEFT JOIN b ON b.kefuid = a.id 
LEFT JOIN d ON d.kefuid = a.id 
WHERE a.brand = '1' and a.is_del = 0 and a.is_lizhi = 0 
GROUP BY a.id 
HAVING 
    SUM(CASE WHEN d.shoukuan_month = '2023-05' and d.is_del = 0 THEN d.netincome ELSE 0 END) >= (CASE WHEN b.month = '2023-05' THEN b.create_income ELSE 30000 END) 
    AND SUM(CASE WHEN d.shoukuan_month = '2023-04' AND d.is_del = 0 THEN d.netincome ELSE 0 END) >= (CASE WHEN b.month = '2023-04' THEN b.create_income ELSE 30000 END)