php - MySQL 查询 - 间隔求和

Question

关闭。此题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题.

已关闭 6 年前。

Improve this question

Stack Overflow 社区您好!

我的 MySQL 查询遇到了一些问题。我所追求的是一个查询，该查询将从表中返回所有 Project_Cost 的总和，但是我希望它将每个时间范围拆分为不同的部分。对我来说，最重要的是我希望它们按月分开(例如:六月、七月、八月等的所有费用)

我目前设置的方式是以一个月为增量分割它们，但这由当前日期决定。所以本质上这只能在本月的第一天起作用，从那时起它就不再准确了。

有人可以帮我编辑这个查询，以便它能给我我想要的东西吗？我已经测试了很多我见过的方法，但没有任何方法能够满足我的需求。

SELECT SUM(IF(DateSigned BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -1 MONTH)) AND NOW(),Project_Cost,0)) AS A,
    SUM(IF(DateSigned BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -2 MONTH)) AND DATE(DATE_ADD(NOW(), INTERVAL -1 MONTH)),Project_Cost,0)) AS B,
    SUM(IF(DateSigned BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -3 MONTH)) AND DATE(DATE_ADD(NOW(), INTERVAL -2 MONTH)),Project_Cost,0)) AS C,
    SUM(IF(DateSigned BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -4 MONTH)) AND DATE(DATE_ADD(NOW(), INTERVAL -3 MONTH)),Project_Cost,0)) AS D,
    SUM(IF(DateSigned BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -5 MONTH)) AND DATE(DATE_ADD(NOW(), INTERVAL -4 MONTH)),Project_Cost,0)) AS E,
    SUM(IF(DateSigned BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -6 MONTH)) AND DATE(DATE_ADD(NOW(), INTERVAL -5 MONTH)),Project_Cost,0)) AS F,
    SUM(IF(DateSigned BETWEEN DATE(DATE_ADD(NOW(), INTERVAL -7 MONTH)) AND DATE(DATE_ADD(NOW(), INTERVAL -6 MONTH)),Project_Cost,0)) AS G
    FROM project
    WHERE program = '1';

Answer 1

您可以使用YEAR(date)*12+MONTH(date)来比较月份:

SELECT SUM(IF(YEAR(DateSigned)*12+MONTH(DateSigned) = YEAR(Now())*12+MONTH(Now()),Project_Cost,0)) AS CurrentMonth,
       SUM(IF(YEAR(DateSigned)*12+MONTH(DateSigned) = YEAR(Now())*12+MONTH(Now())-1,Project_Cost,0)) AS PreviousMonth,
       SUM(IF(YEAR(DateSigned)*12+MONTH(DateSigned) = YEAR(Now())*12+MONTH(Now())-2,Project_Cost,0)) AS C
       /* etc... */
FROM   project
WHERE  program = '1'