tidb 5.0.3
视图创建成功了，查询的时候报错了。
报错信息：
mysql> select * from sdb_core_monitor limit 10;
ERROR 1105 (HY000): line 1 column 7021 near "AS ord LEFT JOIN (SELECT shuidi_sdb_crm.external_insurance_order.id,shuidi_sdb_crm.external_insurance_rm.external_insurance_order.user_id,shuidi_sdb_crm.external_insurance_order.display_id,shuidi_sdb_crm.external_insurance_order.external_displaal_insurance_order.origin,shuidi_sdb_crm.external_insurance_order.money,shuidi_sdb_crm.external_insurance_order.refund_amount,`shuidi_sdb_cr
mysql> s
→ \s
视图内容：
create view sdb_core_monitor as
select case when channel like ‘kmbx%’ then ‘其他’
when is_long=1 and is_crm_order=1 then ‘网电销’
when is_long=0 or (is_long=1 and is_crm_order=0) then ‘平台业务’
end as first_order_channel_group,
case when channel like ‘kmbx%’ then ‘其他’
when is_long=1 and is_crm_order=1 and agent_type=1 then ‘自建’
when is_long=1 and is_crm_order=1 and agent_type=2 then ‘共建’
when is_long=1 and is_crm_order=1 and agent_type=3 then ‘加盟’
when is_long=1 and is_crm_order=1 then ‘无职场’
when is_long=0 then ‘平台-短险’
when is_long=1 and is_crm_order=0 then ‘平台-长险’
end as sec_order_channel_group,
case when channel like ‘kmbx%’ then ‘其他’
when is_long=1 and is_crm_order=1 and agent_type=1 then ‘自建’
when is_long=1 and is_crm_order=1 and agent_type=2 then ‘共建’
when is_long=1 and is_crm_order=1 and agent_type=3 then ‘加盟’
when is_long=1 and is_crm_order=1 then ‘无职场’
when is_long=1 and is_crm_order=0 and product_name = ‘e养添年养老年金保险’ then ‘长险-养老金’
when is_long=1 and is_crm_order=0 and channel like ‘sdbbtt%’ then ‘长险-投放’
when is_long=1 and is_crm_order=0 then ‘长险-其他’
when is_long=0 and parent_policy_no <> ‘’ then ‘短险-续保’
when is_long=0 and is_crm_order=1 then ‘短险-保’
when is_long=0 and channel in (‘cf’,‘jkbz’,‘cfjk’,‘cfjkgj’,‘sdcjk’,‘sdcjkgj’,‘jksh’,‘sdhz’) then ‘短险-筹’
when is_long=0 and (channel in (‘feed’,‘sem’) or channel like ‘BD%’ or channel like ‘sdbbtt%’) then ‘短险-投放’
else ‘短险-保’ end third_order_channel_group,
case when channel like ‘kmbx%’ then ‘其他’
when is_long=1 and is_crm_order=1 and agent_type=1 then ‘自建’
when is_long=1 and is_crm_order=1 and agent_type=2 then ‘共建’
when is_long=1 and is_crm_order=1 and agent_type=3 then ‘加盟’
when is_long=1 and is_crm_order=1 then ‘无职场’
when is_long=1 and is_crm_order=0 and product_name = ‘e养添年养老年金保险’ then ‘长险-养老金’
when is_long=1 and is_crm_order=0 and channel like ‘sdbbtt%’ then ‘长险-分销’
when is_long=1 and is_crm_order=0 then ‘长险-其他’
when is_long=0 and parent_policy_no <> ‘’ then ‘短险-续保’
when is_long=0 and is_crm_order = 1 then ‘电销’
when is_long=0 and channel in (‘cf’,‘jkbz’,‘cfjk’,‘cfjkgj’,‘sdcjk’,‘sdcjkgj’,‘jksh’,‘sdhz’) then ‘短险-筹’
when is_long=0 and (channel in (‘feed’,‘sem’) or channel like ‘BD%’ or channel like ‘sdbbtt%’) and channel like ‘sdbbtt%’ then ‘短险-分销’
when is_long=0 and (channel in (‘feed’,‘sem’) or channel like ‘BD%’ or channel like ‘sdbbtt%’) and (channel in (‘feed’,‘sem’) or channel like ‘BD%’) then ‘短险-H5’
when (channel like ‘%sdjk%’ or channel like ‘%sdik%’) then ‘健康’
when (channel=‘sdbaoapp’ or (platform in (3,4) and channel in (‘default_channel’,‘sdbapp’,‘sdbao’,‘sdbaoapp’))) then ‘保APP’
when (channel like ‘qxyy%’ or channel like ‘qywx%’) then ‘企信’
when channel like ‘mcn%’ then ‘内容获客’
when (sub_channel like ‘BA_NO_C1_Dx_back%’
or sub_channel LIKE ‘%ZH%’
or sub_channel LIKE ‘%BAM_NO_C1_zhaohuiMessages%’
or sub_channel LIKE ‘BA_NO_C1_MB%’
or sub_channel LIKE ‘BAM_NO_C1_m10%’
or sub_channel LIKE ‘BA_NO_C1_Messages%’
or sub_channel LIKE ‘BA_NO_C1_Template%’
or sub_channel LIKE ‘%BA_NO_C1_yuebaonei_0730%’
– or landing_sub_channel like ‘%BAM_NO_C1_zhaohuiMessages%’
– or landing_sub_channel like ‘%BA_NO_C1_Messages%’
) then ‘短信’
when channel like ‘sdbao%’ then ‘保公众号’
else ‘保公众号’ end as fourth_order_channel_group,
order_no,
policy_time,
origin,
is_crm_order
from
(select ord.order_no
,ord.parent_policy_no
,ord.platform
,ord.policy_time
,ord.origin
,case
when date(ord.policy_time) >= ‘2020-09-01’ and ord.platform in (23, 24) and main_ord.order_source not like ‘sdjk%’ then ‘sdjkapp’
else main_ord.order_source
end as channel
, main_ord.sub_order_source as sub_channel
,case when crm.order_no is not null then 1 else 0 end as is_crm_order
,case when ins_type.long_short_type is null then ord.is_long else ins_type.long_short_type end as is_long
,‘’ as agent_type
,‘’ as product_name
from (select *,
case when datediff(invalid_time, effect_time) > 366 then 1 else 0 end as is_long
from shuidi_sdb_crm.insurance_order
where status in (3,4,6,11,12,30) and valid=1 and order_type <> 1) ord
left join (select * from shuidi_sdb_crm.external_insurance_order )main_ord on ord.external_display_id = main_ord.display_id
left join shuidi_sdb_crm.sdb_crm_order crm on ord.order_no = crm.order_no
left join (select case term_type
when 2 then 0
when 3 then 0
when 1 then 1
end as long_short_type,
product_no
from shuidi_sdb_crm.sdb_product) ins_type on ord.base_product_no = ins_type.product_no ) t1 ;
