SELECT
center,
account,
SUM(expenses)/SUM(users) AS result
FROM
(
SELECT
t2.center AS center,
t1.users AS users,
CASE
WHEN t2.currency = 'USD' THEN (t2.expenses*70)
WHEN t2.currency = 'MYR' THEN (t2.expenses*17)
WHEN t2.currency = 'AUD' THEN (t2.expenses*50)
WHEN t2.currency = 'INR' THEN t2.expenses
ELSE 'invalid'
END AS expenses,
t2.currency AS currency,
t2.account AS account
FROM
table1 t1
JOIN
table2 t2
ON(t1.center=t2.center)
) joined_data
GROUP BY center,account
ORDER BY center,account
1条答案
按热度按时间tktrz96b1#
如果您也指定了输出值,那么就很容易理解了。对于您的问题,是的,您可以使用case在一个查询中实现转换
我没有测试这个,但它应该工作。在最坏的情况下,它可能会抛出一个小的语法错误,您应该能够处理它。:)