首页 / MYSQL / MySQL：子查询中的值总和

MySQL：子查询中的值总和

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL：子查询中的值总和，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4631字，纯文字阅读大概需要7分钟。

内容图文

我有关于学校田径的信息,包括学校,季节,现金流量和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_group,如下所示：

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.谢谢！

解决方法:

一些建议：

>加入四季.联接导致左表中的行重复,因此可以通过求和汇总将它们相加两次.如有疑问,请不带分组依据运行查询,例如学校.
>您必须使用诸如inner_schools.id = external_schools.id之类的内容将子查询与外部查询相关联
>但据我所知,您根本不需要子查询

例如：

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

对于多个类别,您可以使用一个案例：

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：子查询中的值总和全部内容，希望文章能够帮你解决MySQL：子查询中的值总和所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/886737.html

来源：【匿名】