请教mysql 如何按时间排序后,超过N条数据的早期记录? 示例:DELETE a FROM a4 a WHERE 4>(SELECT COUNT(*) FROM a4 WHERE a.time<=time) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 贴建表及插入记录的SQL,及要求结果出来看看应该可以 -- 导出 表 fox.ut_mail 结构CREATE TABLE IF NOT EXISTS `ut_mail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `playerid` varbinary(20) NOT NULL, `sendname` varchar(20) DEFAULT NULL, `title` varchar(100) DEFAULT NULL, `content` text, `sendtime` datetime DEFAULT NULL, `readit` smallint(6) NOT NULL DEFAULT '0', `items` text, PRIMARY KEY (`id`), KEY `playerid` (`playerid`), KEY `index0_name` (`sendname`)) ENGINE=InnoDB AUTO_INCREMENT=4285 DEFAULT CHARSET=utf8;-- 正在导出表 fox.ut_mail 的数据:~523 rows (大约)/*!40000 ALTER TABLE `ut_mail` DISABLE KEYS */;INSERT INTO `ut_mail` (`id`, `playerid`, `sendname`, `title`, `content`, `sendtime`, `readit`, `items`) VALUES (3639, _binary 0x7864657667736E303030306D5132624939, 'system_mail', '888', '2013-12-10 10:15:10', 0, '{"zp":5588,"ex":5842,"jb":20000}'), (4284, _binary 0x7864657667733730304730686334444944, 'system_mail', '888', '2014-01-09 10:04:21', 0, '{"zp":5588,"ex":5842,"jb":20000,"30800":1}');/*!40000 ALTER TABLE `ut_mail` ENABLE KEYS */; 需要按 sendtime 排序后,删除超过 12条记录的老数据! SELECT * FROM table WHERE time<(SELECT MIN(time) FROM (SELECT * FROM table ORDER BY time DESC LIMIT n) AS AA) DELETE FROM table WHERE time<(SELECT MIN(time) FROM (SELECT * FROM table ORDER BY time DESC LIMIT n) AS AA) 从内网mysql如何数据同步到公网mysql,求解决方案… 在命令行里导入数据~~? MySQL Query Cache 请假一个表分区的问题 已经使用rpm方式安装好了mysql,怎样添加berkeleyDB(BDB)引擎? 怎么赋予某用户mysq的查询权限 麻烦朋友们帮我解决下 谢谢! mysql update问题 使用标准sql语句,查找一个数据库文件中有多少个表的命令是什么? 请问 如何获得mysql的原码 这样的MySql触发器怎么写 关于mysql删除重复数据的问题
应该可以
-- 导出 表 fox.ut_mail 结构
CREATE TABLE IF NOT EXISTS `ut_mail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`playerid` varbinary(20) NOT NULL,
`sendname` varchar(20) DEFAULT NULL,
`title` varchar(100) DEFAULT NULL,
`content` text,
`sendtime` datetime DEFAULT NULL,
`readit` smallint(6) NOT NULL DEFAULT '0',
`items` text,
PRIMARY KEY (`id`),
KEY `playerid` (`playerid`),
KEY `index0_name` (`sendname`)
) ENGINE=InnoDB AUTO_INCREMENT=4285 DEFAULT CHARSET=utf8;-- 正在导出表 fox.ut_mail 的数据:~523 rows (大约)
/*!40000 ALTER TABLE `ut_mail` DISABLE KEYS */;
INSERT INTO `ut_mail` (`id`, `playerid`, `sendname`, `title`, `content`, `sendtime`, `readit`, `items`) VALUES
(3639, _binary 0x7864657667736E303030306D5132624939, 'system_mail', '888', '2013-12-10 10:15:10', 0, '{"zp":5588,"ex":5842,"jb":20000}'),
(4284, _binary 0x7864657667733730304730686334444944, 'system_mail', '888', '2014-01-09 10:04:21', 0, '{"zp":5588,"ex":5842,"jb":20000,"30800":1}');
/*!40000 ALTER TABLE `ut_mail` ENABLE KEYS */;