两表一对多，left join ,右表只取符合记录的前N条，左表为一，右表为多，

[northenwolf1]

sql如何写
CREATE TABLE `topic` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `comment` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`topicId` int(11) DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `topic` VALUES ('1', '话题1');
INSERT INTO `topic` VALUES ('2', '话题2');

INSERT INTO `comment` VALUES ('1', '1', 't1c1');
INSERT INTO `comment` VALUES ('2', '1', 't1c2');
INSERT INTO `comment` VALUES ('3', '2', 't2c1');
INSERT INTO `comment` VALUES ('4', '2', 't2c2');
INSERT INTO `comment` VALUES ('5', '2', 't2c3');

[ACMAIN_CHM]

参考下贴中的多种方法 http://blog.csdn.net/acmain_chm/article/details/4126306 [征集]分组取最大N条记录方法征集，及散分....

[northenwolf1]

select * from ( SELECT tmp.id, tmp.commentId, @aid:=tmp.id as aid, if(@aid=tmp.id,@rank:=@rank+1,@rank:=1) as rank FROM ( SELECT a.id, b.id commentId FROM topic a LEFT JOIN `comment` b ON a.id = b.topicId ORDER BY b.id ) tmp, (SELECT @aid:= null , @rank:=0) t ) f where f.rank <=2

[verejava]

高级查询 SELECT http://www.verejava.com/?id=1717413210274

[qq_39099280]

select * from
(select * from topic limit 0,n) a --取符合记录的前几条
left join comment b on a.id=b.topicId