匹配ID为MySQL的所有相关行的总和 [英] Sum of All Related Rows with Matching ID MySQL

问题描述

我有以下表格模式:

    tbl_portfolio
    ----------
    id (auto number)
    name

-

    tbl_registration
    ----------------
    id(auto number)
    name
    portfolio_id (FK to tbl_portfolio.id)

-

    tbl_fund
    ---------
    id (auto number)
    registration_id (FK to tbl_registration.id)

-

     tbl_transaction
     ---------------
     id (auto number)
     fund_id (FK to tbl_fund.id)
     type
     shares
     price

我需要创建一个查询，该查询以伪代码执行以下操作:

I need to create a query that in psuedo-code would do the following:

      SELECT port.*, SUM ALL transactions for each portfolio,
      FROM tbl_portfolio port
      INNER JOIN tbl_registration reg ON reg.portfolio_id = port.id
      LEFT JOIN tbl_fund fund on fund.registration_id = reg.id
      LEFT JOIN tbl_transaction trans ON trans.fund_id = fund.id

现在该查询当然行不通了...我本质上需要的是将每种基金的所有价格*单位求和，然后对每次注册将这些总和求和，然后汇总每个投资组合的所有金额.

Now of course that query won't work...What I am needing essentially is to sum all the Price * Units for each fund, and then sum those together for each registration, and then sum all of that together for each portfolio.

每个投资组合可以有多个注册，每个注册可以有多个基金，并且每个基金可以有多个交易.

Each portfolio can have multiple registrations, and each registration can have multiple funds, and each fund can have multiple transactions.

最后一个抛出问题的项目，可能要计算10或100个投资组合，因此我不知道如何编写查询，更不用说以不依赖子查询的有效方式编写查询了会导致它的性能严重下降.

The last item that is throwing a stickler in this, there may be 10's or 100's of portfolios to count so I have no idea how to write the query, much less write it in an effective way that is not relying on subqueries that would cause it to have severely poor performance.

谢谢您的帮助！

PinnyM的答案有效并且可以正确查询数据-但是我应该满足所有需求.

PinnyM's answer works and queries the data correctly - however I should expand on the full need.

除了tbl_transaction之外，还有tbl_distri和tbl_div.两者在tbl_fund.id中都具有FK的fund_id.我需要获取tbl_distri.amount和tbl_div.units的SUM. 因此，完整的伪查询将达到以下效果:

Besides the tbl_transaction there is also a tbl_distri and tbl_div. Both have fund_id as FK to tbl_fund.id . I need to get the SUM's of tbl_distri.amount and tbl_div.units. So the full psuedo query would be something to the effect of:

      SELECT port.*, SUM ALL transactions for each portfolio, SUM(div.units), SUM(distri.amount)
      FROM tbl_portfolio port
      INNER JOIN tbl_registration reg ON reg.portfolio_id = port.id
      LEFT JOIN tbl_fund fund on fund.registration_id = reg.id
      LEFT JOIN tbl_transaction trans ON trans.fund_id = fund.id
      LEFT JOIN tbl_distri distri on distri.fund_id = fund.id
      LEFT JOIN tbl_div div on div.fund_id = fund.id

推荐答案

您是否尝试过使用SUM()?

SELECT port.*, SUM(trans.shares * trans.price) AS transaction_totals
  FROM tbl_portfolio port
  INNER JOIN tbl_registration reg ON reg.portfolio_id = port.id
  LEFT JOIN tbl_fund fund on fund.registration_id = reg.id
  LEFT JOIN tbl_transaction trans ON trans.fund_id = fund.id
  GROUP BY port.id

这篇关于匹配ID为MySQL的所有相关行的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！