再次求教大佬们 mysql 昨天的问题我勉强写出来了，但是有没有更好的方法呢

[chaodada]

select temp.* from ( SELECT COUNT(user_id) as count ,DATE_SUB(NOW(), INTERVAL 6 DAY) as count_time FROM test WHERE reg_time<DATE_SUB(NOW(), INTERVAL 6 DAY) UNION ALL SELECT COUNT(user_id) as count ,DATE_SUB(NOW(), INTERVAL 5 DAY) as count_time FROM test WHERE reg_time< DATE_SUB(NOW(), INTERVAL 5 DAY) UNION ALL SELECT COUNT(user_id) as count ,DATE_SUB(NOW(), INTERVAL 4 DAY) as count_time FROM test WHERE reg_time< DATE_SUB(NOW(), INTERVAL 4 DAY) UNION ALL SELECT COUNT(user_id) as count ,DATE_SUB(NOW(), INTERVAL 3 DAY) as count_time FROM test WHERE reg_time< DATE_SUB(NOW(), INTERVAL 3 DAY) UNION ALL SELECT COUNT(user_id) as count ,DATE_SUB(NOW(), INTERVAL 2 DAY) as count_time FROM test WHERE reg_time< DATE_SUB(NOW(), INTERVAL 2 DAY) UNION ALL SELECT COUNT(user_id) as count ,DATE_SUB(NOW(), INTERVAL 1 DAY) as count_time FROM test WHERE reg_time< DATE_SUB(NOW(), INTERVAL 1 DAY) UNION ALL SELECT COUNT(user_id) as count , DATE_SUB(NOW(), INTERVAL 0 DAY) as count_time FROM test WHERE reg_time< DATE_SUB(NOW(), INTERVAL 0 DAY) ) as temp

求大佬们指教一下

这是数据库 结构以及数据

-- -- 数据库： test

---

-- -- 表的结构 test

CREATE TABLE test ( user_id int(11) NOT NULL, reg_time datetime NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- -- 转存表中的数据 test

INSERT INTO test (user_id, reg_time) VALUES (1, '2020-01-21 08:08:08'), (2, '2020-01-22 00:00:00'), (3, '2020-01-23 08:08:08'), (4, '2020-01-24 08:08:08'), (5, '2020-01-25 08:08:08'), (6, '2020-01-26 08:08:08'), (7, '2020-01-27 08:08:08'), (8, '2020-01-28 00:00:00'), (9, '2020-01-29 09:09:09'), (10, '2020-01-30 13:08:08'), (11, '2020-01-31 15:08:05'), (12, '2020-02-01 17:08:08'), (13, '2020-02-02 21:00:00'), (14, '2020-02-03 05:08:09');

-- -- 转储表的索引

-- -- 表的索引 test

ALTER TABLE test ADD PRIMARY KEY (user_id);

-- -- 在导出的表使用 AUTO_INCREMENT

-- -- 使用表 AUTO_INCREMENT test

ALTER TABLE test MODIFY user_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15; COMMIT;

[iyiluo]

先把大问题分解成小问题，sql 只做查询，不要在 sql 里面做太多计算，计算统一在应用里面。
reg_time 为什么不直接传个日期过去，替换 DATE_SUB(...)？

[iyiluo]

@iyiluo 对了，如果不了解数据库引擎，大部分情况下用 Innodb 是没问题的，除非你有使用 MyISAM 的必要

[rekulas]

多年使用 mysql 的经验是，复杂查询能将字段单独拉出来做索引表的就要拉出来，联合查询再怎么优化，也有瓶颈(确切的说比单独索引表差很多)

[lolizeppelin]

转 pg 解千愁