如何在MySQL中生成数据？?mysql
这是我的SQL：
SELECT COUNT(id), CONCAT(YEAR(created_at), '-', MONTH(created_at), '-', DAY(created_at)) FROM my_table GROUP BY YEAR(created_at), MONTH(created_at), DAY(created_at) 我希望即使没有创建ID的日子也能显示行。现在，在没有活动的日子里，我错过了很多约会。
关于如何更改此查询来做到这一点的任何想法？
写回答
-
在一个查询中执行此操作的方法：
SELECT COUNT(my_table.id) AS total, CONCAT(YEAR(dates.ddate), '-', MONTH(dates.ddate), '-', DAY(dates.ddate)) FROM ( -- Creates "on the fly" 65536 days beginning from 2000-01-01 (179 years) SELECT DATE_ADD("2000-01-01", INTERVAL (b1.b + b2.b + b3.b + b4.b + b5.b + b6.b + b7.b + b8.b + b9.b + b10.b + b11.b + b12.b + b13.b + b14.b + b15.b + b16.b) DAY) AS ddate FROM (SELECT 0 AS b UNION SELECT 1) b1, (SELECT 0 AS b UNION SELECT 2) b2, (SELECT 0 AS b UNION SELECT 4) b3, (SELECT 0 AS b UNION SELECT 8) b4, (SELECT 0 AS b UNION SELECT 16) b5, (SELECT 0 AS b UNION SELECT 32) b6, (SELECT 0 AS b UNION SELECT 64) b7, (SELECT 0 AS b UNION SELECT 128) b8, (SELECT 0 AS b UNION SELECT 256) b9, (SELECT 0 AS b UNION SELECT 512) b10, (SELECT 0 AS b UNION SELECT 1024) b11, (SELECT 0 AS b UNION SELECT 2048) b12, (SELECT 0 AS b UNION SELECT 4096) b13, (SELECT 0 AS b UNION SELECT 8192) b14, (SELECT 0 AS b UNION SELECT 16384) b15, (SELECT 0 AS b UNION SELECT 32768) b16 ) dates LEFT JOIN my_table ON dates.ddate = my_table.created_at GROUP BY dates.ddate ORDER BY dates.ddate 仅当您要测试并且没有在问题上指示“ my_table”时，才需要下一个代码：
create table
my_table(idint (11),created_atdate ); insert intomy_table(id,created_at) values('1','2000-01-01'); insert intomy_table(id,created_at) values('2','2000-01-01'); insert intomy_table(id,created_at) values('3','2000-01-01'); insert intomy_table(id,created_at) values('4','2001-01-01'); insert intomy_table(id,created_at) values('5','2100-06-06');来源：stack overflow2020-05-17 18:11:54赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
