即使数据不存在，Mysql也可以选择逐月记录

I wrote a query to get month-wise record in user table as follows

我写了一个查询来获取用户表中的逐月记录,如下所示

SELECT COUNT( `userID` ) AS total, DATE_FORMAT( `userRegistredDate` , '%b' ) AS
MONTH , YEAR( `userRegistredDate` ) AS year
FROM `users`
GROUP BY DATE_FORMAT( FROM_UNIXTIME( `userRegistredDate` , '%b' ) )

Output:

total      MONTH    year
---------------------------
3           May     2013
2           Jul     2013
--------------------------

Expected Output:

total      MONTH    year
---------------------------
 0          Jan     2013
 0          Feb     2013
 0          Mar     2013
 0          Apr     2013
 3          May     2013
 0          Jun     2013 
 2          Jul     2013
--------------------------

I need to show the record even if data not exist. How to do this?

即使数据不存在,我也需要显示记录。这该怎么做?

2 个解决方案

#1

I won't say much about efficiency as I have not tested it against other methods but without having a temp table this seem a fair way to go.

我不会对效率说太多,因为我没有对其他方法进行测试,但没有临时表,这似乎是一个公平的方法。

   SELECT COUNT(u.userID) AS total, m.month
     FROM (
           SELECT 'Jan' AS MONTH
           UNION SELECT 'Feb' AS MONTH
           UNION SELECT 'Mar' AS MONTH
           UNION SELECT 'Apr' AS MONTH
           UNION SELECT 'May' AS MONTH
           UNION SELECT 'Jun' AS MONTH
           UNION SELECT 'Jul' AS MONTH
           UNION SELECT 'Aug' AS MONTH
           UNION SELECT 'Sep' AS MONTH
           UNION SELECT 'Oct' AS MONTH
           UNION SELECT 'Nov' AS MONTH
           UNION SELECT 'Dec' AS MONTH
          ) AS m
LEFT JOIN users u 
ON MONTH(STR_TO_DATE(CONCAT(m.month, ' 2013'),'%M %Y')) = MONTH(u.userRegistredDate)
   AND YEAR(u.userRegistredDate) = '2013'
GROUP BY m.month
ORDER BY 1+1;

If you make the union based on a date format you can even reduce the work and load on the query.

如果基于日期格式进行联合,则甚至可以减少查询的工作量和负载。

   SELECT COUNT(u.userID) AS total, DATE_FORMAT(merge_date,'%b') AS month, YEAR(m.merge_date) AS year
     FROM (
           SELECT '2013-01-01' AS merge_date
           UNION SELECT '2013-02-01' AS merge_date
           UNION SELECT '2013-03-01' AS merge_date
           UNION SELECT '2013-04-01' AS merge_date
           UNION SELECT '2013-05-01' AS merge_date
           UNION SELECT '2013-06-01' AS merge_date
           UNION SELECT '2013-07-01' AS merge_date
           UNION SELECT '2013-08-01' AS merge_date
           UNION SELECT '2013-09-01' AS merge_date
           UNION SELECT '2013-10-01' AS merge_date
           UNION SELECT '2013-11-01' AS merge_date
           UNION SELECT '2013-12-01' AS merge_date
          ) AS m
LEFT JOIN users u 
       ON MONTH(m.merge_date) = MONTH(u.userRegistredDate)
          AND YEAR(m.merge_date) = YEAR(u.userRegistredDate)
GROUP BY m.merge_date
ORDER BY 1+1;

Live DEMO of both queries.

两个查询的现场演示。

#2

You may need a table to hold every "month" record. A temp table can do the trick:

您可能需要一个表来保存每个“月”记录。临时表可以做到这一点:

drop table if extists temp_months;
create temporary table temp_months
    month date,
    index idx_date(month);
insert into temp_months
    values ('2013-01-31'), ('2013-02-28'), ...

And now, you can left join your data with this newly created temp table:

现在,您可以使用这个新创建的临时表将数据连接起来:

SELECT 
    COUNT( `userID` ) AS total, 
    DATE_FORMAT( m.month , '%b' ) AS
    MONTH , 
    YEAR( m.month ) AS year
FROM 
    months as m
    left join `users` as u on m.month = last_day(FROM_UNIXTIME(`userRegistredDate`, '%b' )
GROUP BY 
    last_day(m.month);

Notice that you can put the temp table creation (and fill) in a stored procedure.

请注意,您可以将临时表创建(和填充)放在存储过程中。

I use last_day for simplicity, but you are free to use any date in the month that you like, if you join it correctly.

为简单起见,我使用last_day,但如果您正确加入,则可以*使用您喜欢的月份中的任何日期。

Hope this helps

希望这可以帮助

#1