2009-11-07admin

mysql 中子查询的3个问题

【准备】

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中分组后取各分组的最新的两组数据

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

【问题三】取每个帖子的最新回复

SELECT * 
FROM (
SELECT * 
FROM ticket_reply
ORDER BY reply_created DESC
) newtb
GROUP BY ticket_id
ORDER BY reply_created DESC