本文介绍了基于另一个表的MySQL SELECT SUM的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一张 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 ;)

SQL Fiddle

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

Results:

|    budgetid_fk | SUM(R.quantity) |             total |       budgetid | budget_month |
|----------------|-----------------|-------------------|----------------|--------------|
| BU201803000001 |               7 | 575.2840143424692 | BU201803000001 |   2018-03-01 |

这篇关于基于另一个表的MySQL SELECT SUM的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-23 13:10