case when a.id='1' then left join tab

I'm sorry, but the query you provided is incomplete and contains syntax errors. Can you please provide more context and details about the query you are trying to write or the problem you are trying to solve? I'll do my best to assist you.

相关问题

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}`，需要在使用时进行替换。同时，这段代码中所用到的数据表和字段名称也需要根据实际场景进行调整。