MySQL:子查询中的总和值

2022-01-09 00:00:00 sql sum subquery mysql

我有关于学校运动的信息，包括学校、赛季、现金流和 cashflow_group 的表格.我正在尝试在用户指定的范围内查询一个或多个给定 cashflow_groups 中现金流量的所有学校.我需要在同一个查询中查询多个不同的类别.我遇到了麻烦.

I have information on school athletics, with tables for school, season, cashflow, and cashflow_group. I'm trying to query for all schools with cashflow in one or more given cashflow_groups within a user-specified range. I need to query multiple different categories in the same query. I'm having trouble.

我的查询如下.我这样做的原因是我可以对多个现金流组求和，并且我认为它有效，直到我仔细观察并看到它将所有学校的现金流量相加为total_cashflow_amount，而每个学校应该有不同的总数，即其相关的现金流行的总和.

My query is below. The reason I did it this way is that I can sum multiple cashflow groups, and I thought it worked until I looked closely and saw that it sums the cashflow amount for all schools as total_cashflow_amount, when each school should have a different total, the sum of its associated cashflow rows.

SELECT distinct schools.*, 
  (SELECT sum(`cashflows`.amount) AS cf FROM `schools` 
  INNER JOIN `seasons` ON seasons.school_id = schools.id 
  INNER JOIN `cashflows` ON cashflows.season_id = seasons.id 
  INNER JOIN `cashflow_groups` ON `cashflow_groups`.id = `cashflows`.cashflow_group_id 
  WHERE ((`cashflow_groups`.id = 12) AND (`seasons`.`year` = 2010))) AS total_branding_cashflow 
FROM `schools` 

INNER JOIN `seasons` ON seasons.school_id = schools.id 
INNER JOIN `cashflows` ON cashflows.season_id = seasons.id 
INNER JOIN `cashflow_groups` ON `cashflow_groups`.id = `cashflows`.cashflow_group_id 
INNER JOIN `seasons` seasons_schools ON seasons_schools.school_id = schools.id 
WHERE (`seasons`.`year` = 2010) 
GROUP BY schools.id 
HAVING (total_branding_cashflow BETWEEN 50000000 AND 100000000) 
ORDER BY schools.name ASC LIMIT 0, 50

在此查询中，total_branding_cashflow 是所有学校 的总数.我不知道如何在子查询中单独获取每所学校的总数.

In this query, total_branding_cashflow is the total for all schools. I can't figure out how to get the total for each school individually in the subquery.

就目前而言，我得到的结果类似于

As it stands, I get a result like

| school.id | … | total_branding_cashflow |
|     2     |   |       900000            |
|     5     |   |       900000            |

当我想要的时候

| school.id | … | total_branding_cashflow |
|     2     |   |       500000            |
|     5     |   |       400000            |

在子查询中添加一个 GROUP BY 可以让我在单独的行中获得每所学校的现金流的总和，但子查询仅在它们提供单行时才有效，所以这对我没有帮助.

Adding a GROUP BY to the subquery gives me the sum of each school's cashflow in a separate row, but subqueries only work when they give a single row, so this doesn't help me.

我错过了什么?我想使用子查询的原因是我希望能够同时查找多个不同的 cashflow_groups，如下所示:

What am I missing? The reason I though to use subqueries is that I want to be able to look up multiple different cashflow_groups at the same time, like this:

SELECT distinct schools.*, 
  (SELECT sum(`cashflows`.amount) AS cf FROM `schools` 
  INNER JOIN `seasons` ON seasons.school_id = schools.id 
  INNER JOIN `cashflows` ON cashflows.season_id = seasons.id 
  INNER JOIN `cashflow_groups` ON `cashflow_groups`.id = `cashflows`.cashflow_group_id 
WHERE ((`cashflow_groups`.id = 12) AND (`seasons`.`year` = 2010)) ) AS total_branding_cashflow, 
  (SELECT sum(`cashflows`.amount) AS cf FROM `schools` 
  INNER JOIN `seasons` ON seasons.school_id = schools.id 
  INNER JOIN `cashflows` ON cashflows.season_id = seasons.id 
  INNER JOIN `cashflow_groups` ON `cashflow_groups`.id = `cashflows`.cashflow_group_id 
WHERE ((`cashflow_groups`.id = 1) AND (`seasons`.`year` = 2010)) ) AS total_ticket_sales_cashflow,
  (SELECT sum(`cashflows`.amount) AS cf FROM `schools` 
  INNER JOIN `seasons` ON seasons.school_id = schools.id 
  INNER JOIN `cashflows` ON cashflows.season_id = seasons.id 
  INNER JOIN `cashflow_groups` ON `cashflow_groups`.id = `cashflows`.cashflow_group_id 
WHERE ((`cashflow_groups`.id = 7) AND (`seasons`.`year` = 2010)) ) AS total_university_cashflow 
FROM `schools` 
INNER JOIN `seasons` ON seasons.school_id = schools.id 
INNER JOIN `cashflows` ON cashflows.season_id = seasons.id 
INNER JOIN `cashflow_groups` ON `cashflow_groups`.id = `cashflows`.cashflow_group_id 
INNER JOIN `seasons` seasons_schools ON seasons_schools.school_id = schools.id 
WHERE (`seasons`.`year` = 2010) 
GROUP BY schools.id 
HAVING (total_branding_cashflow BETWEEN 50000000 AND 100000000) AND 
       (total_ticket_sales_cashflow BETWEEN 50000000 AND 100000000) AND 
       (total_university_cashflow BETWEEN 0 AND 10000000) 
ORDER BY schools.name ASC LIMIT 0, 50

我认为我无法使用不在其自己的子查询中的 SUM 来执行此操作.我正在开发一个 Rails 应用程序，并且可能会想出一种通过 ruby​​ 代码执行此操作的方法.但这似乎不正确，如果可能的话，我宁愿用 SQL 把它写下来.谢谢！

I didn't think I could do this with a SUM that isn't in its own subquery. I'm working on a rails app, and could probably come up with a way to do this via ruby code. But that doesn't seem right and I'd prefer to get it down in SQL if possible. Thanks!

推荐答案

一些建议:

  • 加入一次seasons.连接会导致左表中的行重复，因此它们可以通过 sum 聚合进行两次求和.如有疑问，请针对示例学校运行不带 group by 的查询.
  • 您必须将子查询与外部查询相关联，例如 inner_schools.id = outer_schools.id
  • 但据我所知，您根本不需要子查询
  • Join on seasons once. A join causes rows from the left table to be duplicated, so they can be summed twice by the sum aggregate. When in doubt, run the query without group by for an example school.
  • You'd have to relate the subquery to the outer query with something like inner_schools.id = outer_schools.id
  • But as far as I can see, you don't need a subquery at all

例如:

SELECT  schools.*
,       sum(cashflows.amount) total_branding_cashflow
FROM    schools
JOIN    seasons
ON      seasons.school_id = schools.id 
        and seasons.year = 2010
JOIN    cashflows
ON      cashflows.season_id = seasons.id 
        and cashflow_group_id = 12
GROUP BY 
        schools.id 
HAVING  total_branding_cashflow BETWEEN 50000000 AND 100000000

对于多个类别，您可以使用一个案例:

For multiple categories, you could use a case:

SELECT  schools.*
,       sum(case when cashflow_group_id = 1 then cashflows.amount end) total1
,       sum(case when cashflow_group_id = 12 then cashflows.amount end) total12
FROM    schools
JOIN    seasons
ON      seasons.school_id = schools.id 
        and seasons.year = 2010
JOIN    cashflows
ON      cashflows.season_id = seasons.id 
GROUP BY 
        schools.id

相关文章