在select语句中使用IF，逐月对值进行分组

I am trying to run a report that will group by month the totals of expense accounts from an expense table which has the followings columns on each row:

我正在尝试运行一个报告,该报告将按费用表格逐月分组费用帐户,每个行包含以下列:

expense_acc
debit
credit
post_date

The desired output of the report is in the following column format: EXP ACC - JAN - FEB - MAR

报告的所需输出采用以下列格式:EXP ACC - JAN - FEB - MAR

This is my SQL select query:

这是我的SQL选择查询:

SELECT expense_acc,
if(MONTH(post_date)=1,SUM(expenses.debit-expenses.credit),0) AS 'JAN',
if(MONTH(post_date)=2,SUM(debit-credit),0) AS 'FEB',
if(MONTH(post_date)=3,SUM(debit-credit),0) AS 'MAR'
FROM expenses
WHERE YEAR(expenses.entered)='2016'
GROUP BY expenses.expense_acc

The results are not grouping the expense values by month as expected. I am seeing grouping in the first row, regardless of the transaction date.

结果不按预期按月对费用值进行分组。无论交易日期如何,我都会在第一行看到分组。

3 个解决方案

#1

You have two parts to your requirement.

根据您的要求,您有两个部分。

A month-by-month aggregate of your table's contents

表格内容的逐月汇总

A pivot table rendering, in which you pivot by-month rows to lie in columns.

一个数据透视表呈现,您可以按月将行旋转到列中。

Also, SUM(expenses.debit-expenses.credit) isn't resilient if the debit or credit columns ever contain NULL values.

此外,如果借方或贷方列包含NULL值,则SUM(expenses.debit-expenses.credit)不具有弹性。

Also, YEAR(date) defeats any index on date.

此外,YEAR(日期)在日期中击败任何指数。

If you're wise you'll handle these requirements in two steps. For one thing, it will be easier to troubleshoot your results. For another, the next person who comes along will better understand your project.

如果你是明智的,你将分两步处理这些要求。首先,对结果进行故障排除会更容易。另一方面,下一个出席的人将更好地了解您的项目。

The month-by-month aggregate:

每月汇总:

       SELECT expense_acc, LAST_DAY(post_date) month_ending,
              SUM(expenses.debit) - SUM(expenses.credit) as net_expenses
         FROM expenses
        WHERE post_date >= '2016-01-01'
          AND post_date < '20016-12-31' + INTERVAL 1 DAY
        GROUP BY expense_acc, LAST_DAY(post_date)
        ORDER BY expense_acc, LAST_DAY(post_date)

This will give you a row for each account and date. The row will show the account, the month-ending date, and the net expenses. (I don't understand expenses.entered in your example. It's best to filter on the same date you use to make your aggregate.) Your auditors will appreciate this separation of logic.

这将为每个帐户和日期提供一行。该行将显示帐户,月末日期和净费用。 (我不了解费用。在你的例子中有。最好在你用来制作聚合的同一天过滤。)你的审计员会理解这种逻辑分离。

Next, you can use this as a subquery, to make your pivot display.

接下来,您可以将其用作子查询,以显示您的数据透视表。

That's pretty straightforward:

这非常简单:

 SELECT expense_acc,
        SUM(IF(MONTH(month_ending)=1,net_expenses,0)) as jan,
        SUM(IF(MONTH(month_ending)=2,net_expenses,0)) as feb,
        SUM(IF(MONTH(month_ending)=3,net_expenses,0)) as mar, 
        ...
   FROM (
       SELECT expense_acc, LAST_DAY(post_date) month_ending,
              SUM(expenses.debit) - SUM(expenses.credit) as net_expenses
         FROM expenses
        WHERE post_date >= '2016-01-01'
          AND post_date < '20016-12-31' + INTERVAL 1 DAY
        ) z
  GROUP BY expense_acc
  ORDER BY expense_acc

But, you may want to do the pivoting in a client program. It's notoriously hard to write and maintain MySQL pivot code.

但是,您可能希望在客户端程序中进行透视。编写和维护MySQL数据透视代码非常困难。

#2

SELECT 
SUM(JAN) AS JAN,
SUM(FEB) AS FEB
SUM(MAR) AS MAR
FROM
(

SELECT expense_acc,
if(MONTH(post_date)=1,SUM(expenses.debit-expenses.credit),0) AS 'JAN',
if(MONTH(post_date)=2,SUM(debit-credit),0) AS 'FEB',
if(MONTH(post_date)=3,SUM(debit-credit),0) AS 'MAR'
FROM expenses
WHERE YEAR(expenses.entered)='2016'
GROUP BY expenses.expense_acc
)TMP
GROUP BY expense_acc

#3

In my opinion, the optimal way is that:

在我看来,最佳方式是:

SELECT expense_acc, MONTH(post_date) month_num, SUM(debit-credit) as total
FROM expenses
WHERE YEAR(expenses.entered) = '2016'
GROUP BY expenses.expense_account, MONTH(post_date)

That will give you something like

这会给你类似的东西

|expense account |   mont_num   | total |
-----------------------------------------
|    2345        |       1      |   50  |
-----------------------------------------
|    2345        |       2      |   30  |
-----------------------------------------
|    2346        |       1      |   45  |

...

Because if you are doing one if statement per month, you will get 12 ifs. It's better to manage the format of rows in you controller.

因为如果你每月做一个if语句,你会得到12个ifs。最好管理控制器中的行格式。

Hope it will help you.

希望它会对你有所帮助。

#1