MySQL中 CASE WHEN THEN then后面跟常量是没问题的 如果改成字段就没值

[Supersaiyaren丶]

代码如下 ：

SELECT a.comm_type,
CASE a.comm_type
WHEN 1 THEN b.pro_com_money
WHEN 2 THEN c.dra_com_money
WHEN 3 THEN d.sale_com_money
WHEN 4 THEN e.res_com_money
ELSE 0 END AS comMoney ,
a.roy_status ,a.up_dt,

CASE WHEN a.comm_type = 1 THEN 10000
WHEN a.comm_type = 2 THEN 1000
END

FROM nsap_bone.finance_bonus a
LEFT JOIN nsap_bone.finance_produce_bonus b ON a.order_no = b.pro_order
LEFT JOIN nsap_bone.finance_draw_bonus c ON c.dra_order = a.order_no
LEFT JOIN nsap_bone.finance_sale_bonus d ON d.sale_order = a.order_no
LEFT JOIN nsap_bone.finance_research_bonus e ON e.sale_order = a.order_no
LEFT JOIN nsap_base.base_user f ON f.user_id = a.apply_user_id

把 b.pro_com_money 改成100 是可以查出来的 但是改成字段就 出 NULL 是不是when 后面不能接变量！！！ 求解惑

[Supersaiyaren丶]

引用 1 楼 hdhai9451 的回复:

SELECT a.comm_type,
CASE a.comm_type
    WHEN  1 THEN isnull(b.pro_com_money,0) --空值转换为0
    WHEN  2 THEN c.dra_com_money
    WHEN  3 THEN d.sale_com_money
    WHEN  4 THEN e.res_com_money
ELSE 0 END AS comMoney ,
 a.roy_status ,a.up_dt,

CASE WHEN a.comm_type = 1 THEN 10000
WHEN a.comm_type = 2 THEN 1000
END 

FROM nsap_bone.finance_bonus a
LEFT JOIN nsap_bone.finance_produce_bonus b ON a.order_no = b.pro_order
LEFT JOIN nsap_bone.finance_draw_bonus c ON c.dra_order = a.order_no
LEFT JOIN nsap_bone.finance_sale_bonus d ON d.sale_order = a.order_no
LEFT JOIN nsap_bone.finance_research_bonus e ON e.sale_order = a.order_no
LEFT JOIN nsap_base.base_user f ON f.user_id = a.apply_user_id

是有值的 不是空 只是查询的时候把值转换成 NULL 了~！

[KeepSayingNo]

Then后面是可以跟字段的，你看我用的例子就得出正确结果

[Andy__Huang]

SELECT a.comm_type,
CASE a.comm_type
    WHEN  1 THEN isnull(b.pro_com_money,0) --空值转换为0
    WHEN  2 THEN c.dra_com_money
    WHEN  3 THEN d.sale_com_money
    WHEN  4 THEN e.res_com_money
ELSE 0 END AS comMoney ,
 a.roy_status ,a.up_dt,

CASE WHEN a.comm_type = 1 THEN 10000
WHEN a.comm_type = 2 THEN 1000
END 

FROM nsap_bone.finance_bonus a
LEFT JOIN nsap_bone.finance_produce_bonus b ON a.order_no = b.pro_order
LEFT JOIN nsap_bone.finance_draw_bonus c ON c.dra_order = a.order_no
LEFT JOIN nsap_bone.finance_sale_bonus d ON d.sale_order = a.order_no
LEFT JOIN nsap_bone.finance_research_bonus e ON e.sale_order = a.order_no
LEFT JOIN nsap_base.base_user f ON f.user_id = a.apply_user_id