请教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 Adminitrator的登陆问题 mysql_real_connect连接的数据库ip和穿的参数不一致??? group by 以后使用limit的问题 求助:MYSQL时间导入格式问题 Hibernate一对多集合映射应用遇到的问题 已经郁闷了一天了,请各位高人帮我看看! 上传MYSQL数据库的问题 如何用php实现postgres数据库的备份?不好意思,没分了。 为什么中文存入到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 */;