基于另一个表的MySQL SELECT SUM

本文介绍了基于另一个表的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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！