mysql select * from A join B on case when then

  SELECT B.ORDER_NUM, B.TOTAL_AMOUNT_PAYABLE, B.SETTLE_PRICE, B.SETTLEMENT_STATUS,B.RETURN_GOODS_AMOUNT,
B.MCHT_INCOME, B.MM_INCOME, A.PAY_SUMBIT_TIME, A.COMMISION, A.AMOUNT
FROM (
SELECT PAY_SUMBIT_TIME, COMMISION,COMMISION_RATE, AMOUNT, EXT, ORDER_ID, ORDER_NO, CHANNEL
FROM BASE_PAY_FLOW_LOG
WHERE IS_DELETE = “N”
AND PAY_STATUS=”S”
AND CHECK_STATUS=”S”
AND PAY_SETTLE_STATUS=”S”
) A JOIN (
SELECT ORDER_NUM, ORDER_ID, TOTAL_AMOUNT_PAYABLE,SETTLE_PRICE, SETTLEMENT_STATUS,RETURN_GOODS_AMOUNT,
MCHT_INCOME, MM_INCOME
FROM OM_ORDER
WHERE STATE=”5″
AND IS_CLEAR_SETTLE_FINISH=”Y”
AND SETTLEMENT_STATUS=”S”
AND SETTLE_FINISH_TIME IS NOT NULL
) B
on  case when A.EXT = “orderNum” then A.ORDER_NO = B.ORDER_NUM
when A.EXT = “orderId” then A.ORDER_ID = B.ORDER_ID
end
大家帮看看哪里出错了？为什么执行不了，把里面的子查询 A， B 单独执行就可以返回结果。放一起，加上 case when  then 就不行了，奇怪，求指导。

解决方案

case 不是这样用的。case 不应该理解为 if 语法，而应该被理解为 iif 函数。
把on后面改成 ((A.EXT = “orderNum” AND A.ORDER_NO = B.ORDER_NUM) OR (A.EXT = “orderId” AND A.ORDER_ID = B.ORDER_ID)) 应该可以执行。

改成这样：
case when a.EXT = “orderNum” then a.ORDER_NO
when a.EXT = “orderId” then a.ORDER_ID
end = case when a.EXT = “orderNum” then b.ORDER_NUM
when a.EXT = “orderId” then b.ORDER_ID
end

 case when A.EXT = "orderNum" then A.ORDER_NO = B.ORDER_NUM
         when A.EXT = "orderId" then A.ORDER_ID = B.ORDER_ID
    END

改为

((A.EXT = "orderNum"  AND A.ORDER_NO = B.ORDER_NUM) OR (A.EXT = "orderId" and A.ORDER_ID = B.ORDER_ID))