这是我的问题
SELECT currency_code, SUM(
CASE
WHEN TYPE = 'buy'
THEN to_amount
END ) AS BUY, SUM(
CASE
WHEN TYPE = 'sell'
THEN to_amount
END ) AS SELL, SUM(
CASE
WHEN TYPE = 'sell'
THEN rate
END ) AS SELL_RATE, SUM(
CASE
WHEN TYPE = 'buy'
THEN rate
END ) AS BUY_RATE
FROM tb_currency
LEFT JOIN tb_bill ON tb_currency.CURRENCY_ID = tb_bill.CURRENCY_ID
AND tb_bill.TYPE
IN (
'buy', 'sell'
)
输出是
现在我想把BUY字段中的值和BUY_RATE字段中的值分开。我之前尝试了几个查询,得到了错误消息“未知列类型BUY”。
如何解决这个问题?
最佳答案
你需要做一个子选择,像这样
SELECT *, (BUY/BUY_RATE) AS DIV_VALUE FROM (
SELECT currency_code, SUM(
CASE
WHEN TYPE = 'buy'
THEN to_amount
END ) AS BUY, SUM(
CASE
WHEN TYPE = 'sell'
THEN to_amount
END ) AS SELL, SUM(
CASE
WHEN TYPE = 'sell'
THEN rate
END ) AS SELL_RATE, SUM(
CASE
WHEN TYPE = 'buy'
THEN rate
END ) AS BUY_RATE
FROM tb_currency
LEFT JOIN tb_bill ON tb_currency.CURRENCY_ID = tb_bill.CURRENCY_ID
AND tb_bill.TYPE
IN (
'buy', 'sell'
)
)
关于mysql - 如何使用mysql查询进行划分,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/4989976/