根據使用者來獲取渠道

with all_user_id
(
select user_id
from t1
union
select user_id
from t2
)
---獲取渠道
all_channel
(
select case when t6.user_cate in ('1','2') then xx
when t3.agent_cate in ('1','2') then xx
when t8.agent_cate in ('1','2') then xx
end as channel
from all_user_id t1
inner join ac_user t6 ---使用者表
on t1.user_id = t6.user_id
left join t_company t9 ---機構表
on t6.organ_id = t9.organ_id
inner join (select * from cust_system_user ---系統使用者主題
where user_type_desc in ('a','b','c')
and system_code = 'XXX')t2
on t1.user_id = t2.user_id
left join (select * from cust_system_user ---系統使用者主題
where user_type_desc in ('a','b','c')
and system_code = 'XXX')t3
on t2.user_code = t3.user_code
left join staff_field_agent ---代理人維表
on t3.user_code = t4.agent_code
left join t_company t5 ---機構表
on t4.organ_id = t5.organ_id
)
select
from t1.xx,
t1.yy,
t2.channel
from t1
left join all_channel t2
on t1.user_id = t2.user_id