select * from (select distinct substr(managecom,1,4) as managecom from t_ees_mis_13fut_list where mis_modifydate ='2023-06-01' ) a left join ( select substr(managecom,1,4) managecom, sum(tb_prem),sum(init_prem), sum(tb_prem) / sum(init_prem) as tbrate from t_ees_mis_13fut_list where mis_modifydate ='2023-06-01' and stat_date = '2023-07-01' group by substr(managecom,1,4) ) b on a.managecom = b.managecom left join ( select substr(managecom,1,4) managecom, sum(tb_prem),sum(init_prem), sum(tb_prem) / sum(init_prem) as tbrate from t_ees_mis_13fut_list where mis_modifydate ='2023-06-01' and stat_date = '2023-08-01' group by substr(managecom,1,4) ) b1 on a.managecom = b1.managecom优化

这段 SQL 查询语句可以进行如下优化：

1. 将子查询中的 `distinct` 关键字去掉，使用 `group by` 代替。

2. 将两个子查询中的条件 `mis_modifydate ='2023-06-01'` 提取出来，放到外层查询中。

3. 将两个子查询中的相同部分提取出来，使用 `with` 子句定义一个临时表。

4. 将 `left join` 改为 `inner join`，因为 `managecom` 在两个子查询中都出现过，所以不存在 `null` 值。

优化后的 SQL 查询语句如下：

with tmp_table as (
  select substr(managecom,1,4) as managecom,
         sum(case when stat_date = '2023-07-01' then tb_prem else 0 end) as tb_prem_1,
         sum(case when stat_date = '2023-07-01' then init_prem else 0 end) as init_prem_1,
         sum(case when stat_date = '2023-08-01' then tb_prem else 0 end) as tb_prem_2,
         sum(case when stat_date = '2023-08-01' then init_prem else 0 end) as init_prem_2
  from t_ees_mis_13fut_list
  where mis_modifydate ='2023-06-01'
  group by substr(managecom,1,4)
)

select a.managecom, b.tb_prem_1, b.init_prem_1, b.tb_prem_1 / b.init_prem_1 as tbrate_1, 
                   b1.tb_prem_2, b1.init_prem_2, b1.tb_prem_2 / b1.init_prem_2 as tbrate_2
from tmp_table a 
inner join tmp_table b on a.managecom = b.managecom
inner join tmp_table b1 on a.managecom = b1.managecom
where mis_modifydate ='2023-06-01'
and b.stat_date = '2023-07-01'
and b1.stat_date = '2023-08-01'