MySQL:子查询中的总和值
我有关于学校体育的信息,包括学校、季节、现金流和 cashflow_group 的表格。我正在尝试查询在用户指定范围内的一个或多个给定 cashflow_groups 中拥有现金流的所有学校。我需要在同一个查询中查询多个不同的类别。我有麻烦了。
我的查询如下。我这样做的原因是我可以对多个现金流组进行求和,我认为它有效,直到我仔细观察并发现它将所有学校的现金流量求和为total_cashflow_amount,而每个学校应该具有不同的总计,即其相关现金流行的总和。
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
是所有学校的总计。我不知道如何在子查询中单独获取每所学校的总数。
就目前情况而言,我得到的结果就像
| school.id | … | total_branding_cashflow |
| 2 | | 900000 |
| 5 | | 900000 |
我想要的那样,
| school.id | … | total_branding_cashflow |
| 2 | | 500000 |
| 5 | | 400000 |
在子查询中添加 GROUP BY 可以在单独的行中给出每所学校现金流的总和,但子查询仅在给出单行时才起作用,所以这不会帮我。
我缺少什么?我使用子查询的原因是我希望能够同时查找多个不同的 cashflow_groups,如下所示:
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 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.
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
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 |
when what I want is
| school.id | … | total_branding_cashflow |
| 2 | | 500000 |
| 5 | | 400000 |
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.
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
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!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
一些建议:
季节
一次。连接会导致左表中的行重复,因此可以通过sum
聚合将它们求和两次。如有疑问,请针对示例学校运行不带group by
的查询。inner_schools.id = external_schools.id
的内容将子查询与外部查询相关联,:
例如 多个类别,您可以使用案例:
Some suggestions:
seasons
once. A join causes rows from the left table to be duplicated, so they can be summed twice by thesum
aggregate. When in doubt, run the query withoutgroup by
for an example school.inner_schools.id = outer_schools.id
For example:
For multiple categories, you could use a case: