MySQL 相关项目
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;
Select Language