mysql casewhen结果集_Mysql case when 和 多表 union all 多表不同字段到同一结果集...

currencyId AS currencyId,

SELECT

userName AS userName,

agentName AS agentName,

(case

when currencyType=1 then 'CNY'

when currencyType=2 then 'THB'

when currencyType=3 then 'KRW'

when currencyType=4 then 'IDR'

else 'CNY' end ) AS currencyType,

SUM(settleAccount)*-1 AS settleAccount,

SUM(IF(bettingAmount>0,1,0)) AS orderNumber,

SUM(bettingAmount) AS bettingAmount,

SUM(winAmount) AS winAmount,

SUM(lossAmount) AS lossAmount,

SUM(winLossAmount) AS winLossAmount,

SUM(effectiveAmount) AS effectiveAmount,

SUM(specialBonus) AS specialBonus,

SUM(deposit) AS deposit,

SUM(withdrawals) AS withdrawals,

SUM(additionAmount) AS additionAmount

FROM (

SELECT

u_id AS userName,

agent_id AS agentName,

currency_id AS currencyType,

win_user AS settleAccount,

bet_money AS bettingAmount,

IF(win_user>0,win_user,0) AS winAmount,

IF(win_user<0,win_user,0) AS lossAmount,

win_user AS winLossAmount,

IF((return_user!=0 OR return_spartner!=0) AND

win_user!=0,SOLID_MONEY,0) AS effectiveAmount,

0 AS specialBonus,

0 AS deposit,

0 AS withdrawals,

0 AS additionAmount

FROM w_bet FORCE INDEX(start_time)

WHERE win_user!=0

AND start_time>='2014-11-02 00:00:00'

AND start_time<='2014-11-03 23:59:59'

AND agent_id IN ('dta0002','d206025')

UNION ALL

SELECT

to_uid AS userName,

agent_id AS agentName,

'0' AS currencyType,

0 AS settleAccount,

0 AS bettingAmount,

0 AS winAmount,

0 AS lossAmount,

0 AS winLossAmount,

0 AS effectiveAmount,

IF(cost_remark='PAY_ADD' OR

cost_remark='ACTIVITY_ADD',cost_amount,0) AS specialBonus,

IF(cost_remark='deposit' OR

cost_remark='turnin',cost_amount,0) AS deposit,

0 AS withdrawals,

0 AS additionAmount

FROM w_cash c,w_user u

WHERE u_id = to_uid

AND u.agent_id IN

('dta0002','d206025')

AND cost_time>='2014-11-02

00:00:00'

AND cost_time<='2014-11-03

23:59:59'

UNION ALL

SELECT

d.u_id AS userName,

agent_id AS agentName,

'0' AS currencyType,

0 AS settleAccount,

0 AS bettingAmount,

0 AS winAmount,

0 AS lossAmount,

0 AS winLossAmount,

0 AS effectiveAmount,

0 AS specialBonus,

0 AS deposit,

IF(admin_words='取消優惠' OR

admin_words='CANCEL_ACTIVITY_ADD',draw_remark,draw_money) AS

withdrawals,

0 AS additionAmount

FROM w_draw d,w_user u

WHERE d.u_id = u.u_id

AND  u.agent_id IN

('dta0002','d206025')

AND draw_change_time>='2014-11-02 00:00:00'

AND draw_change_time<='2014-11-03 23:59:59'

) b

GROUP BY userName