【准备】
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | CREATE TABLE `ticket_reply` ( `reply_id` INT(11) NOT NULL AUTO_INCREMENT, `ticket_id` INT(11) NOT NULL, `reply_created` datetime DEFAULT NULL, `reply_content` text, PRIMARY KEY (`reply_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records -- ---------------------------- INSERT INTO `ticket_reply` VALUES ('1', '2', '2009-11-04 20:17:59', '11111'); INSERT INTO `ticket_reply` VALUES ('2', '2', '2009-11-04 20:22:09', '22222'); INSERT INTO `ticket_reply` VALUES ('3', '1', '2009-11-04 20:22:19', '33333'); INSERT INTO `ticket_reply` VALUES ('4', '3', '2009-11-04 20:22:28', '4444'); INSERT INTO `ticket_reply` VALUES ('5', '4', '2009-11-04 20:22:38', '5555'); INSERT INTO `ticket_reply` VALUES ('6', '1', '2009-11-04 20:22:49', '32'); INSERT INTO `ticket_reply` VALUES ('7', '1', '2009-11-04 20:23:18', '11'); INSERT INTO `ticket_reply` VALUES ('8', '1', '2009-11-04 20:23:26', '43'); INSERT INTO `ticket_reply` VALUES ('9', '2', '2009-11-04 20:23:41', '3'); INSERT INTO `ticket_reply` VALUES ('10', '3', '2009-11-04 20:23:50', '1'); INSERT INTO `ticket_reply` VALUES ('11', '3', '2009-11-04 20:24:02', '11'); INSERT INTO `ticket_reply` VALUES ('12', '4', '2009-11-04 20:24:12', 'f'); INSERT INTO `ticket_reply` VALUES ('13', '4', '2009-11-04 20:24:24', 'a'); |
【问题一】
在在In/all/any/sime等子查询里使用limit时会报错：
如下所示SQL
SELECT * FROM `ticket_reply` WHERE reply_id IN (SELECT reply_id * FROM `ticket_reply` LIMIT 3);
报错如下：
#1235 – This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
解决方案，添加一层查询，
SELECT * FROM `ticket_reply` WHERE reply_id IN (SELECT reply_id FROM (SELECT * FROM `ticket_reply` LIMIT 3) tb )
如果不加别名，则会显示
#1248 – Every derived table must have its own alias
【问题二】
mysql中分组后取各分组的最新的两组数据
1 2 3 4 5 6 7 8 9 | SELECT s1. * FROM ticket_reply AS s1 WHERE ( SELECT COUNT( 1 ) FROM ticket_reply AS s2 WHERE s2.reply_created >= s1.reply_created AND s2.ticket_id = s1.ticket_id ) <= 2 ORDER BY ticket_id, reply_created DESC |
【问题三】
取每个帖子的最新回复
1 2 3 4 5 6 7 8 | SELECT * FROM ( SELECT * FROM ticket_reply ORDER BY reply_created DESC ) newtb GROUP BY ticket_id ORDER BY reply_created DESC |