基于另一个表的MySQL SELECT SUM [英] MySQL SELECT SUM based on another table
问题描述
我有一张 MySQL表
tb_currencies
currencyid | currency_name
CU0001 | IDR
CU0002 | SGD
CU0003 | b
$ b tb_currency_converters currencyconverterid | currency_month | from_currencyid_fk | to_currencyid_fk |金额
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 |价格
IT0001 |鼠标| CU0001 | 165000
IT0002 |键盘| CU0002 | 20
IT0003 |电视LCD | CU0003 | 350
tb_pro_request
requestid | budgetid_fk | itemid_fk |数量
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.inve stmentid_fk，
R.remarks，
R.approval_status，
I.itemid，
I.item_name，
I.价格，
SUM（R。数量）* I.价格总额，
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
您可以看到有以下不同货币的3个项目。
我想要的货币IDR，USD转换为SGD。然后SUM（它是1行）
解决方案我想我在你的FK里搞了一点，但至少你有这个精神;）
9 / 82da57 / 36rel =nofollow noreferrer> SQL小提琴
$ b MySQL 5.6架构设置：
$ b 查询1 ：
SELECT
R.budgetid_fk，
SUM（R.quantity），
SUM（R.quantity * I.price * COALESCE（CC.amount，1）），
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）|总| | budgetid | budget_month |
| ---------------- | ----------------- | ---------- --------- | ---------------- | -------------- |
| BU201803000001 | 7 | 575.2840143424692 | BU201803000001 | 2018-03-01 |
I have an table on 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
Example:
My departmentid_fk is: DP0003, means I have Budget with Currency SGD.
On tb_pro_request, there are 2 items transaction different currency(IDR & USD) with Budget Department Currency(SGD).
What I want is, that 2 items with different currency need to convert first to SGD (Due to my Department Budget is: SGD) then SUM it.
*Logic, if the currency is SGD then no need to convert, but if not SGD then convert it first using tb_currency_converters then SUM it.
Is it possible to do in directly to query?
My query code so far:
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
You can see there are 3 items with different currency below.
What I want, convert to SGD for currency IDR, USD. then SUM it(to be 1 row)
解决方案 I think I mess up a bit in your FK, but at least you have the spirit ;)
MySQL 5.6 Schema Setup:
Query 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 |
这篇关于基于另一个表的MySQL SELECT SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
