mysql - SQL CASE 和 WHERE 以及连接

Question

我写了这样的sql

"SELECT (
    CASE WHEN (1.00 - sys_pay.org_rate)*trans.txn_amt as agent_pay_current 
    < (trans.txn_amt - sys_pay.org_rate_limit) as agent_pay_max 
    THEN agent_pay_max 
    ELSE agent_pay_current END),
    FROM sys_org 
    INNER JOIN trans ON sys_org.mid = trans.mid 
    INNER JOIN sys_pay ON sys_pay.mid = sys_org.mid 
    ORDER BY txn_date DESC limit 10"

它说我的 SQL 语法有错误，如何纠正？

Answer 1

尝试这样，

"SELECT (
           CASE 
                WHEN ((1.00 - sys_pay.org_rate) * trans.txn_amt) < (trans.txn_amt - sys_pay.org_rate_limit) THEN 
                     agent_pay_max
                ELSE agent_pay_current
           END
       )
FROM   sys_org
       INNER JOIN trans
            ON  sys_org.mid = trans.mid
       INNER JOIN sys_pay
            ON  sys_pay.mid = sys_org.mid
ORDER BY
       txn_date DESC"

或

"SELECT (
           CASE 
                WHEN ((1.00 - sys_pay.org_rate) * trans.txn_amt) < (trans.txn_amt - sys_pay.org_rate_limit) THEN (trans.txn_amt - sys_pay.org_rate_limit)
                ELSE ((1.00 - sys_pay.org_rate) * trans.txn_amt)
           END
       )            agent_pay_return
FROM   sys_org
       INNER JOIN trans
            ON  sys_org.mid = trans.mid
       INNER JOIN sys_pay
            ON  sys_pay.mid = sys_org.mid
ORDER BY
       txn_date     DESC"

agent_pay_return 将是根据检查的情况返回的值。