过去 12 个月的 Mysql 总和

本文介绍了过去 12 个月的 Mysql 总和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试创建一个查询，以获取基于月份的最近 12 个月的记录以进行图表表示.经过大量阅读和观看此，如果您从一张表中删除一些数据，您就会看到问题.

这是一个可行的解决方案，它可以为您提供所有月份的数据，将两个表中的数据相加，即使只有一个表.这是通过将费用和付款作为单独的查询处理，然后将它们连接在一起来实现的.

SELECT Months.Month, COALESCE(expensedata.ExpenseAmount, 0) AS ExpenseAmount, COALESCE(paymentdata.PaymentAmount, 0) AS PaymentAmount从(SELECT DATE_FORMAT(now(), '%m/%y') AS 月UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 1 MONTH), '%m/%y')UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 2 MONTH), '%m/%y')UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 3 MONTH), '%m/%y')UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 4 MONTH), '%m/%y')UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 5 MONTH), '%m/%y')UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 6 MONTH), '%m/%y')UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 7 MONTH), '%m/%y')UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 8 MONTH), '%m/%y')UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 9 MONTH), '%m/%y')UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 10 MONTH), '%m/%y')UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 11 MONTH), '%m/%y')) AS 月左加入(SELECT SUM(price) AS ExpenseAmount, DATE_FORMAT(date_occurred,'%m/%y') AS Month来自费用哪里 user_id = 1GROUP BY MONTH(date_occurred), YEAR(date_occurred)) 费用数据 ON Months.Month = 费用数据.Month左加入(SELECT SUM(amount) AS PaymentAmount, DATE_FORMAT(date_occurred,'%m/%y') AS Month来自付款哪里 user_id = 1GROUP BY MONTH(date_occurred), YEAR(date_occurred)) Paymentdata ON Months.Month = paymentdata.MonthORDER BY Months.Month;

SQL Fiddle 显示此工作:sqlfiddle/#!2/3f52a8/5

I am trying to create a query to get the last 12 month records based on month for chart representation. After a lot of reading and after watching this similar topic I created a query that seems right but missing the months with 0 money. As an example, I see in my graph months 1/14,2/14,4/14 and so on... 3/14 is missing.

My code is this

SELECT * FROM (SELECT DATE_FORMAT(now(), '%m/%y') AS Month UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 1 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 2 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 3 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 4 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 5 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 6 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 7 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 8 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 9 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 10 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 11 MONTH), '%m/%y') ) AS Months LEFT JOIN (SELECT sum(expenses.price) AS ExpenseAmount, sum(payments.amount) AS PaymentsAmount, DATE_FORMAT(expenses.date_occurred,'%m/%y') AS Month, DATE_FORMAT(payments.date_occurred,'%m/%y') AS Montha FROM expenses, payments WHERE payments.user_id= 1 AND payments.user_id=expenses.user_id GROUP BY MONTH(payments.date_occurred), YEAR(payments.date_occurred) ORDER BY payments.date_occurred ASC ) data ON Months.MONTH = data.Montha ORDER BY data.Montha;

Any help will be great as this kind of queries are too advanced for me :-)

解决方案

As the query looks like it should produce a row for each month, can you check the query output, rather than what your graph is producing? I suspect you've got an entry for 04/14, but that the value is NULL rather than 0. To correct this, you can change the query to start

SELECT Months.Month, COALESCE(data.ExpenseAmount, 0) AS ExpenseAmount, COALESCE(data.PaymentAmount, 0) AS PaymentAmount

COALESCE will give you 0 instead of NULL where there are no rows matching your left join.

However, there are further problems in your query. You will only get rows if there is an expense and a payment in the same month - check sqlfiddle/#!2/3f52a8/1 and you'll see the problem if you remove some of the data from one table.

Here's a working solution which will give you all months, summing the data from both tables, even if only one is present. This works by handling the expenses and payments as separate queries, then joining them together.

SELECT Months.Month, COALESCE(expensedata.ExpenseAmount, 0) AS ExpenseAmount, COALESCE(paymentdata.PaymentAmount, 0) AS PaymentAmount FROM (SELECT DATE_FORMAT(now(), '%m/%y') AS Month UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 1 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 2 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 3 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 4 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 5 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 6 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 7 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 8 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 9 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 10 MONTH), '%m/%y') UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 11 MONTH), '%m/%y') ) AS Months LEFT JOIN (SELECT SUM(price) AS ExpenseAmount, DATE_FORMAT(date_occurred,'%m/%y') AS Month FROM expenses WHERE user_id = 1 GROUP BY MONTH(date_occurred), YEAR(date_occurred)) expensedata ON Months.Month = expensedata.Month LEFT JOIN (SELECT SUM(amount) AS PaymentAmount, DATE_FORMAT(date_occurred,'%m/%y') AS Month FROM payments WHERE user_id = 1 GROUP BY MONTH(date_occurred), YEAR(date_occurred)) paymentdata ON Months.Month = paymentdata.Month ORDER BY Months.Month;

SQL Fiddle showing this working: sqlfiddle/#!2/3f52a8/5