select c.area_name,c.mon,c.count,ifnull(c1.count1,0),ifnull(c1.count1,0)/count * 100 from ( select a.area_name,MONTH(pb.wlpb_create_time) mon,count(pb.id) count from bc_company_info b inner join ( select b.id from wk_ledger_produce_gather g left join bc_company_info b on b.id =g.bci_id where g.wlpg_year =2022 group by b.id HAVING sum(g.wlpg_total_produce) >=30 UNION select b.id from wk_plan_info p left join wk_plan_danger d on d.wpi_id = p.id left join bc_company_info b on b.id = p.bci_id where p.wpi_year = 2022 group by b.id HAVING sum(d.wpd_this_produce) >30) b1 on b1.id = b.id left join wk_ledger_produce_bill pb on pb.bci_id = b.id left join sys_area a on a.id = b.bci_city where pb.wlpb_create_time >'2023-01-01 00:00:00' group by b.bci_city,MONTH(pb.wlpb_create_time) ) c left join ( select a.area_name,MONTH(pb.wlpb_create_time) mon,count(pb.id) count1 from bc_company_info b inner join ( select b.id from wk_ledger_produce_gather g left join bc_company_info b on b.id = g.bci_id where g.wlpg_year =2022 group by b.id HAVING sum(g.wlpg_total_produce) >=30 UNION select b.id from wk_plan_info p left join wk_plan_danger d on d.wpi_id = p.id left join bc_company_info b on b.id = p.bci_id where p.wpi_year = 2022 group by b.id HAVING sum(d.wpd_this_produce) >30) b1 on b1.id = b.id left join wk_ledger_produce_bill pb on pb.bci_id = b.id left join sys_area a on a.id = b.bci_city where pb.wldb_end_from != 1 and pb.wlpb_create_time >'2023-01-01 00:00:00' group by b.bci_city,MONTH(pb.wlpb_create_time)) c1 on c.area_name = c1.area_name and c.mon = c1.mon 这段SQL怎么优化
时间: 2024-04-05 07:35:04 浏览: 23
这段SQL语句比较复杂,可以考虑以下几个方面进行优化:
1. 索引优化:根据EXPLAIN命令的查询计划,确定哪些表需要添加索引,可以在表的关键字段上添加索引,提高查询效率。
2. 减少子查询:可以将子查询中的重复代码提取出来,避免多次重复查询。另外,可以考虑使用临时表或者表变量等方式,将子查询的结果存储在临时表中,然后再进行关联查询,避免多次重复查询。
3. 减少ifnull函数的使用:可以使用LEFT JOIN或者COALESCE函数代替ifnull函数,避免多次重复计算。
4. 确认需要查询的字段:可以根据实际需要,确认需要查询的字段,并且避免查询不必要的字段,提高查询效率。
总之,优化复杂SQL语句需要综合考虑多个方面,并且需要根据具体情况进行调整,从而提高查询效率。