select a.IMSI, a.GJ, a.YYS, count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end) as MRWZGXQQCS, count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 and b.result<>1 then a.IMSI else null) end as MRWZGXQQCS from (select * from spark_odc_dwd.D_ENS_GMAP_MM where p_hour='2023060500') a left join (select * from spark_odc_dwd.D_ENS_GMAP_MM where p_hour='2023060500') b on a.IMSI=b.IMSI group by a.IMSI, a.GJ, a.YYS报错说mismatched input 'when' expecting {')', ','}(line 6, pos 11)，[1.1] failure: identifier matching regex (?i)SHOW expected

这是一段 SQL 语句，看起来有一些语法错误。根据报错信息，可能是在第六行的语句中，case when 后面缺少了一个 end。你可以在第六行的第二个 count 函数后面加上 end，这样就可以避免这个错误了。同时，在 group by 语句中，可能需要把第二个 count 函数的别名也加入到 group by 语句中，以避免语法错误。

相关问题

select a.IMSI, a.GJ, a.YYS, count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end) as MRWZGXQQCS, count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 and b.result<>1 then a.IMSI else null end) as MRWZGXQQCS, case when count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end)=0 then 0 else count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 and b.result<>1 then a.IMSI else null)/ count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end) as MRWZGXCGL, sum(c.MRZJCS) as MRZJCS, sum(c.MRZJHJCGCS) as MRZJHJCGCS, sum(d.MRBJCS) as MRBJCS, sum(d.MRBJHJCGCS) as MRBJHJCGCS, case when sum(c.MRZJCS)=0 then 0 else sum(c.MRZJHJCGCS)/sum(c.MRZJCS) end as MRZJWLJTL, case when sum(c.MRZJCS)=0 then 0 else sum(d.MRBJHJCGCS)/sum(d.MRBJCS) end as MRBJWLJTL, count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 then a.IMSI else null end) as MCWZGXQQCS, count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 and b.result<>1 then a.IMSI else null end) as MCWZGXCGCS, case when count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 then a.IMSI else null end)=0 then 0 else count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 and b.result<>1 then a.IMSI else null end)/ count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 then a.IMSI else null end) as MCWZGXCGL from (select * from TAB_A union select * from TAB_B union select * from TAB_C)a left join (select * from spark_odc_dwd.D_ENS_GMAP_MM where p_hour='#{time yyyyMMddHH}') b on a.IMSI=b.IMSI left join TAB_D c on a.IMSI=c.calling_imsi left join TAB_E c on a.IMSI=c.called_imsi group by a.IMSI, a.GJ, a.YYS

这个SQL语句存在两个问题：

1. `count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 and b.result<>1 then a.IMSI else null end)` 这个计数列在前面已经定义过了，而且它的别名与另一个计数列的别名相同，应该更改其中一个别名。

2. `else count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 and b.result<>1 then a.IMSI else null end)/ count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end)` 这个计算列的语法有误，应该将它单独作为一个计算列，并且在分母为0时设置默认值0。

下面是修改后的SQL语句：

select 
    a.IMSI, 
    a.GJ, 
    a.YYS, 
    count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end) as MRWZGXQQCS, 
    count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 and b.result<>1 then a.IMSI else null end) as MRWZGXCGCS, 
    case when count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end)=0 then 0 
         else count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 and b.result<>1 then a.IMSI else null end) / count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end) 
    end as MRWZGXCGL, 
    sum(c.MRZJCS) as MRZJCS, 
    sum(c.MRZJHJCGCS) as MRZJHJCGCS, 
    sum(d.MRBJCS) as MRBJCS, 
    sum(d.MRBJHJCGCS) as MRBJHJCGCS, 
    case when sum(c.MRZJCS)=0 then 0 
         else sum(c.MRZJHJCGCS)/sum(c.MRZJCS) 
    end as MRZJWLJTL, 
    case when sum(d.MRBJCS)=0 then 0 
         else sum(d.MRBJHJCGCS)/sum(d.MRBJCS) 
    end as MRBJWLJTL, 
    count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 then a.IMSI else null end) as MCWZGXQQCS, 
    count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 and b.result<>1 then a.IMSI else null end) as MCWZGXCGCS, 
    case when count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 then a.IMSI else null end)=0 then 0 
         else count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 and b.result<>1 then a.IMSI else null end) / count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 then a.IMSI else null end) 
    end as MCWZGXCGL 
from  
    (select * from TAB_A union select * from TAB_B union select * from TAB_C) a 
left join  
    (select * from spark_odc_dwd.D_ENS_GMAP_MM where p_hour='#{time yyyyMMddHH}') b on a.IMSI=b.IMSI 
left join  
    TAB_D c on a.IMSI=c.calling_imsi 
left join  
    TAB_E d on a.IMSI=d.called_imsi 
group by  
    a.IMSI, 
    a.GJ, 
    a.YYS

select '#{time yyyyMMdd}0000' TIME_ID, a.IMSI, a.GJ, a.YYS, count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end) as MRWZGXQQCS, count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 and b.result<>1 then a.IMSI else null end) as MRWZGXCGCS, case when count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end)=0 then 0 else count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 and b.result<>1 then a.IMSI else null)/count(case when substr(b.IMSI,1,5)<>46000 and b.operate_code=2 then a.IMSI else null end) as MRWZGXCGL, sum(c.MRZJCS) as MRZJCS, sum(c.MRZJHJCGCS) as MRZJHJCGCS, sum(d.MRBJCS) as MRBJCS, sum(d.MRBJHJCGCS) as MRBJHJCGCS, case when sum(c.MRZJCS)=0 then 0 else sum(c.MRZJHJCGCS)/sum(c.MRZJCS) end as MRZJWLJTL, case when sum(c.MRZJCS)=0 then 0 else sum(d.MRBJHJCGCS)/sum(d.MRBJCS) end as MRBJWLJTL, count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 then a.IMSI else null end) as MCWZGXQQCS, count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 and b.result<>1 then a.IMSI else null end) as MCWZGXCGCS, case when count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 then a.IMSI else null end)=0 then 0 else count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 and b.result<>1 then a.IMSI else null end)/ count(case when substr(b.msc,1,2)<>86 and a.GJ is not null and b.operate_code=2 then a.IMSI else null end) as MCWZGXCGL, now() LOAD_TIME from (select * from TAB_A union select * from TAB_B union select * from TAB_C)a left join (select * from spark_odc_dwd.D_ENS_GMAP_MM where p_hour='#{time yyyyMMddHH}') b on a.IMSI=b.IMSI left join TAB_D c on a.IMSI=c.calling_imsi left join TAB_E d on a.IMSI=d.called_imsi group by a.IMSI, a.GJ, a.YYS

这段 SQL 查询语句看起来是对多个数据表进行联合查询，并进行一些聚合操作，得出一些业务指标。其中包括了一些计数、求和和分组等操作，主要目的是对不同条件下的数据进行统计和分析，以便于业务决策。需要注意的是，这里的 SQL 查询语句中含有一些参数，如 `#{time yyyyMMdd}` 和 `#{time yyyyMMddHH}`，需要在使用时进行替换。同时，这段代码中所用到的数据表和字段名称也需要根据实际场景进行调整。