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');
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');
解决方案 »
- mysql 不能 REPAIR TABLE,所有的表都不能REPAIR,表是好的。
- 请教如何监测mysql服务是否在正常运行
- Linux中不能建立存储过程
- 急求一条SQL,大侠请帮忙!!
- 请问如何转移(复制)数据库?
- 在RH8.0中如何开启远程访问功能?
- 我是一个刚学MySQL的无名小辈,不知道是学MySQL好还是学SQL Server好!还请多指教。
- MySQL无法启动错误1067
- linux下mysq中character_set_databases的编码怎么改成不了utf8?
- 如果一个表按某个字段排序,当多次查询时,是否返回的顺序都是一样的?
- mysql 数据量大的情况下如何优化
- oracle 中 update table1 id=rownum 在 mysql中怎么实现
[征集]分组取最大N条记录方法征集,及散分....
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
(select * from topic limit 0,n) a --取符合记录的前几条
left join comment b on a.id=b.topicId