即使数据不存在，Mysql也会选择按月记录 [英] Mysql to select month-wise record even if data not exist

问题描述

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

SELECT COUNT( `userID` ) AS total, DATE_FORMAT( `userRegistredDate` , '%b' ) ASMONTH , YEAR( `userRegistredDate` ) 作为年份来自`用户`GROUP BY DATE_FORMAT( FROM_UNIXTIME( `userRegistredDate` , '%b' ) )

输出:

总计 MONTH 年---------------------------2013 年 5 月 3 日2013 年 7 月 2 日--------------------------

预期输出:

总计 MONTH 年---------------------------2013 年 1 月 0 日2013 年 2 月 0 日2013 年 3 月 0 日2013 年 4 月 0 日2013 年 5 月 3 日2013 年 6 月 0 日2013 年 7 月 2 日--------------------------

即使数据不存在，我也需要显示记录.如何做到这一点?

解决方案

我不会说太多关于效率的事情，因为我没有针对其他方法测试过它，但没有临时表，这似乎是一个不错的方法.

>

 SELECT COUNT(u.userID) AS total, m.month从 (选择Jan"作为月份UNION SELECT 'Feb' 作为 MONTHUNION SELECT 'Mar' 作为月份UNION SELECT 'Apr' 作为月份UNION SELECT 'May' 作为 MONTHUNION SELECT 'Jun' 作为 MONTHUNION SELECT 'Jul' 作为 MONTHUNION SELECT 'Aug' 作为月份UNION SELECT 'Sep' 作为 MONTHUNION SELECT 'Oct' 作为 MONTHUNION SELECT 'Nov' 作为 MONTHUNION SELECT 'Dec' 作为 MONTH) 作为米左加入用户 uON MONTH(STR_TO_DATE(CONCAT(m.month, '2013'),'%M %Y')) = MONTH(u.userRegistredDate)AND YEAR(u.userRegistredDate) = '2013'按月分组按 1+1 排序；

如果您根据日期格式进行联合，您甚至可以减少查询的工作量和负载.

 SELECT COUNT(u.userID) AS total, DATE_FORMAT(merge_date,'%b') AS 月, YEAR(m.merge_date) AS 年从 (SELECT '2013-01-01' AS 合并日期UNION SELECT '2013-02-01' AS 合并日期UNION SELECT '2013-03-01' AS 合并日期UNION SELECT '2013-04-01' AS 合并日期UNION SELECT '2013-05-01' AS merge_dateUNION SELECT '2013-06-01' AS 合并日期UNION SELECT '2013-07-01' AS 合并日期UNION SELECT '2013-08-01' AS 合并日期UNION SELECT '2013-09-01' AS 合并日期UNION SELECT '2013-10-01' AS 合并日期UNION SELECT '2013-11-01' AS 合并日期UNION SELECT '2013-12-01' AS 合并日期) 作为米左加入用户 u月 (m.merge_date) = 月 (u.userRegistredDate)AND YEAR(m.merge_date) = YEAR(u.userRegistredDate)GROUP BY m.merge_date按 1+1 排序；

两个查询的实时演示.

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?

解决方案

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.

这篇关于即使数据不存在，Mysql也会选择按月记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！