MySQL选择每月记录，即使数据不存在

一尘不染

MySQL选择每月记录，即使数据不存在

mysql

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

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' ) )

输出：

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

预期产量：

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

即使数据不存在，我也需要显示记录。这个怎么做？

阅读 260

2020-05-17

共1个答案

一尘不染

关于效率，我不会说太多，因为我没有针对其他方法进行过测试，但是如果没有临时表，这似乎是一个不错的选择。

   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;

如果您基于日期格式进行并集，甚至可以减少工作量和查询负担。

   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;

两个查询的实时演示。

2020-05-17