问题描述
我有关于学校运动的信息，包括学校、赛季、现金流和 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
seasonsonce. A join causes rows from the left table to be duplicated, so they can be summed twice by thesumaggregate. When in doubt, run the query withoutgroup byfor 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
这篇关于MySQL:子查询中的总和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！