mysql - 过去 12 个月的每月创建帐户

Question

我有一个账户表，其中名为“dateCreation”的列是账户的创建日期。

我想获取过去 12 个月内创建的所有帐户，但有时 2 个月或更长时间都没有创建帐户。在这几个月里，我希望 mysql 返回 0。我的请求工作正常(我在 stackoverflow 上得到它):

 SELECT  customers.dateCreation, Months.id AS `month`, Months.mois, COUNT(customers.dateCreation) AS `count`
FROM 
(
  SELECT 1 as ID, 'Janvier' as mois UNION SELECT 2 as ID, 'Février' as mois UNION  SELECT 3 as ID, 'Mars' as mois UNION SELECT 4 as ID, 'Avril' as mois 
  UNION  
  SELECT 5 as ID, 'Mai' as mois UNION SELECT 6 as ID, 'Juin' as mois UNION SELECT 7 as ID, 'Juillet' as mois UNION SELECT 8 as ID , 'Aout' as mois
  UNION  
  SELECT 9 as ID, 'Septembre' as mois UNION SELECT 10 as ID, 'Octobre' as mois UNION SELECT 11 as ID, 'Novembre' as mois UNION SELECT 12 as ID, 'Decembre' as mois
) as Months
LEFT JOIN customers on Months.id=month(customers.dateCreation) AND dateCreation BETWEEN Date_add(Now(),interval - 12 month) AND NOW()
GROUP BY Months.id
ORDER BY Months.id ASC

但它返回类似的东西:

    ID    |    Count
    1     |      0
    2     |      0
    3     |      0
    4     |      0
    5     |      8
    6     |      1
    7     |      0
    8     |      1
    9     |      0
    10    |      7
    11    |      0
    12    |      4

但是我们在九月，所以我希望第一个月是十月

这是一个 sqlfiddle:http://sqlfiddle.com/#!9/edfeb/1

Answer 1

将您的订单修改为:

ORDER BY
  CASE WHEN Months.id > MONTH(NOW()) -- check if the month is after the current 
       THEN 0                        -- order by months after the current first
       ELSE 1                        -- and then by the months up to the current 
  END,
  Months.id ASC

参见 fiddle