MySql如何比较今年月份与上个月？ [英] MySql how to compare this years month to last years month?

问题描述

如今，今年二月份，与去年二月相比​​，今年二月份的销售额增长了二十多个（今年也将使用YEAR（CURDATE（））而不是2012），一年前。结果表应该只有一列，其中包含的部门编号增加了20％以上的销售额。

How do I find the department numbers who have produced more than 20% more sales in them this February (as in this year so would use YEAR(CURDATE()) rather than saying "2012") compared to last February as in the year before. The result table should have only one column which contains the department numbers that have produced 20% more sales.

如果去年的febraury没有销售部门2，但销售这个febraury，那么0 * 1.2 = 0，所以结果不会显示部门2。如果去年2月份3号出售了1件，而今年2月份出售了2件，这将增加1.5件，超过1.2件，结果应显示为3部。如果只有3个部门，最多应该只有3个行。

If the febraury last year has no sales for "department 2" but sales for this febraury, then 0 * 1.2= 0 so the results would not show "department 2". If the february last year for "department 3" sold one item, and the february this year sold two items, this would be a 1.5 increase which is more than 1.2 so the results should show "department 3". If there were only 3 departments, at most, there should be only 3 rows.

部门2不出现，因为上个月有3个销售额，二月份有一个销售。部门4不出现，因为上个月没有销售，所以在数学0 * 1.2 = 0，所以部门4不会出现，尽管有销售这个febraury。

Department 2 does not appear because there was 3 sales last february and one sale this february. Department 4 does not appear because there was no sale last february so in terms of maths 0 * 1.2 = 0 so Department 4 would not appear despite there being sales this febraury.

谢谢你提前，希望这是足够详细的。

Thank you in advance, and hope this is detailed enough.

推荐答案

尝试使用有如下所示：

    select departement_no
    from department
    group by departement_no
    having sum(case date_format(date, '%m%Y') 
                when CONCAT('02',YEAR(CURDATE())) then 1 else 0 end) 
           >
           1.2 * sum(case date_form(date, '%m%Y') 
                     when CONCAT('02',YEAR(DATE_SUB(CURDATE(),INTERVAL 1 Year)))
                     then 1 else 0 end)
         AND 
         sum(case date_form(date, '%m%Y') 
                     when CONCAT('02',YEAR(DATE_SUB(CURDATE(),INTERVAL 1 Year)))
                     then 1 else 0 end) > 0

这篇关于MySql如何比较今年月份与上个月？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！