select stat_date, 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' group by stat_date,substr(managecom,1,4);把这个sql中stat_date结果集当作查询字段怎么改

如果要将 `stat_date` 的结果集作为查询字段，可以将其放在子查询中，并使用 `AS` 关键字给其取一个别名，然后在外层查询中使用该别名作为查询字段。修改后的 SQL 语句如下：

SELECT t1.stat_date, t1.managecom, t1.tb_prem, t1.init_prem, t1.tb_prem / t1.init_prem AS tbrate
FROM (
    SELECT stat_date, substr(managecom,1,4) managecom, sum(tb_prem) tb_prem, sum(init_prem) init_prem
    FROM t_ees_mis_13fut_list
    WHERE mis_modifydate = '2023-06-01'
    GROUP BY stat_date, substr(managecom,1,4)
) t1;

这样就可以将 `stat_date` 的结果集作为查询字段进行查询了。

相关问题

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'