复杂SQL案例：用户授权渠道查询

供参考：

SELECT 
r.course_id 课程id,
r.user_id 用户ID,
u.user_full_name  姓名,
u.province_name 省名,
u.city_name 城市,
c.card_password 兑换码,
o.order_id  订单,
o.real_fee 实付金额,
o.order_type 订单类型,
o.pay_type 支付方式,
o.device_type 设备类型,
o.order_status 订单状态,
(case when c.user_id is null and o.user_id is null then '有权限无来源'  when o.order_status='已退款' then '有权限无来源'  when c.card_password is not null then '兑换码'  when o.user_id  is not null then '订单' else '' end) 状态检查
from 
(
    select distinct a.course_id,a.user_id,b.sku_id goods_id,a.order_id
    from tetralogy.user_rights_course a left join tetralogy.goods_sku_course b on b.course_id=a.course_id
    where a.current_status='1' 
) r 
  left join 
(
    select
    card_unlock_user_id user_id,
    card_password,
    goods_id
    from tetralogy.card_info
    where 1=1
    and card_is_unlock=0
    and card_is_valid=1
)  c on r.user_id=c.user_id and r.goods_id=c.goods_id
  left join 
(
    select
    user_id,id order_id,goods_ids goods_id,device_type,real_fee,
    (case order_type when 0 then '课程'  when 1 then '解锁码' else order_type end ) order_type,
    (case pay_type when 0 then '微信'  when 1 then '支付宝' when 2 then '苹果' when 3 then '对公转账' else order_type end ) pay_type,
    (case order_status when 0 then '待支付'  when 1 then '已支付' when 2 then '退款中' when 3 then '已退款'  when 9 then '已关闭' else order_status end ) order_status
    from tetralogy.app_order
    where order_status=1
)   o on r.order_id=o.order_id
  left join tetralogy.user_info  u on u.id=r.user_id
where 1=1