即使数据不存在，Mysql也会选择按月记录
2021-11-20 00:00:00
mysql
我写了一个查询来获取用户表中的月度记录，如下所示
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.
相关文章