› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
再次求教大佬们 mysql 昨天的问题我勉强写出来了，但是有没有更好的方法呢
chaodada · 2020-02-04 09:51:01 +08:00 · 3368 次点击这是一个创建于 1746 天前的主题，其中的信息可能已经有所发展或是发生改变。
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;
 |
1
iyiluo 2020-02-04 10:23:42 +08:00
先把大问题分解成小问题，sql 只做查询，不要在 sql 里面做太多计算，计算统一在应用里面。
reg_time 为什么不直接传个日期过去，替换 DATE_SUB(...)？ |
 |
3
rekulas 2020-02-04 13:29:17 +08:00
多年使用 mysql 的经验是，复杂查询能将字段单独拉出来做索引表的就要拉出来，联合查询再怎么优化，也有瓶颈(确切的说比单独索引表差很多)
|
 |
4
lolizeppelin 2020-02-11 11:46:17 +08:00
转 pg 解千愁
|
Select Language