我有一张桌子 MySQL
tb_currencies
currencyid | currency_name
CU0001 | IDR
CU0002 | SGD
CU0003 | USD
tb_currency_converters
currencyconverterid | currency_month | from_currencyid_fk | to_currencyid_fk | amount
CC0001 | 2018-03-01 | CU0001 | CU0002 | 0.00009
CC0002 | 2018-03-01 | CU0002 | CU0001 | 10425
CC0003 | 2018-03-01 | CU0003 | CU0002 | 1.31964
tb_budgets
budgetid | budget_month | departmentid_fk | currencyid_fk
BU201803000001 | 2018-03-01 | DP0003 | CU0002
BU201803000002 | 2018-03-01 | DP0002 | CU0002
tb_items
itemid | item_name | currencyid_fk | price
IT0001 | Mouse | CU0001 | 165000
IT0002 | Keyboard | CU0002 | 20
IT0003 | TV LCD | CU0003 | 350
tb_pro_request
requestid | budgetid_fk | itemid_fk | quantity
RQ201803000001 | BU201803000001 | IT0002 | 1
RQ201803000002 | BU201803000001 | IT0003 | 5
RQ201803000003 | BU201803000001 | IT0004 | 1
例:
我的departmentid_fk
是:DP0003
,表示我的预算为货币SGD
。
在tb_pro_request上,有2个项目使用预算部门的货币(SGD)来交易不同的货币(IDR和USD)。
我想要的是,需要先将具有不同货币的2个项目转换为SGD
(由于我的部门预算为SGD),然后SUM
才进行转换。
*逻辑,如果货币SGD
则无需转换,但如果不是SGD
那么首先将其转换使用tb_currency_converters
,然后SUM
它。
是否可以直接查询?
到目前为止我的查询代码:
SELECT
R.requestid,
R.budgetid_fk,
R.category,
R.itemid_fk,
SUM(R.quantity),
R.request_date,
R.investmentid_fk,
R.remarks,
R.approval_status,
I.itemid,
I.item_name,
I.price,
SUM(R.quantity) * I.price as total,
B.budgetid,
B.budget_month
FROM
tb_pro_request R,
tb_items I,
tb_budgets B
WHERE
R.itemid_fk = I.itemid AND
R.budgetid_fk = B.budgetid AND
R.investmentid_fk = '' AND
B.active = 'Y' AND
(R.approval_status = 'P' OR R.approval_status = 'A') AND
DATE_FORMAT(B.budget_month,'%Y-%m') = '2018-03' AND
B.departmentid_fk = 'DP0003'
GROUP BY I.currencyid_fk
我认为您的FK有点混乱,但至少您有精神;)
MySQL 5.6模式设置:
查询1:
SELECT
R.budgetid_fk,
SUM(R.quantity),
SUM(R.quantity * I.price * COALESCE(CC.amount,1)) as total,
B.budgetid,
B.budget_month
FROM tb_pro_request R
INNER JOIN tb_items I
ON R.itemid_fk = I.itemid
INNER JOIN tb_budgets B
ON R.budgetid_fk = B.budgetid
AND B.active = 'Y'
LEFT JOIN tb_currency_converters CC
ON CC.from_currencyid_fk = I.currencyid_fk
AND CC.to_currencyid_fk = B.currencyid_fk
WHERE
R.investmentid_fk = ''
AND (
R.approval_status = 'P'
OR R.approval_status = 'A'
)
AND DATE_FORMAT(B.budget_month,'%Y-%m') = '2018-03'
AND B.departmentid_fk = 'DP0003'
GROUP BY R.budgetid_fk
结果:
| budgetid_fk | SUM(R.quantity) | total | budgetid | budget_month |
|----------------|-----------------|-------------------|----------------|--------------|
| BU201803000001 | 7 | 575.2840143424692 | BU201803000001 | 2018-03-01 |
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句