bbs论坛与回复查询
mysql 5.5用户表：
CREATE TABLE `t_user` (
`id` int(10) NOT NULL,
`name` varchar(40) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
话题表
CREATE TABLE `t_posts` (
`id` int(10) NOT NULL,
`title` varchar(100) CHARACTER SET utf8 NOT NULL,
`author` int(10) NOT NULL,
`content` text CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
KEY `fk_posts_user_idx` (`author`),
CONSTRAINT `fk_posts_user` FOREIGN KEY (`author`) REFERENCES `t_user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
回复表：（rid是自身表主键的引用）
CREATE TABLE `t_reply` (
`id` int(10) NOT NULL,
`posts_id` int(10) NOT NULL,
`user` int(10) NOT NULL,
`content` varchar(255) COLLATE utf8_bin NOT NULL,
`rid` int(10) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
KEY `fk_posts_replay_from_user_idx` (`user`),
KEY `fk_posts_replay_posts_idx` (`posts_id`),
CONSTRAINT `fk_posts_replay_user` FOREIGN KEY (`user`) REFERENCES `t_user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `fk_posts_replay_posts` FOREIGN KEY (`posts_id`) REFERENCES `t_posts` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
数据：
INSERT INTO `t_user` VALUES (1,'小li'),(2,'xiaowang'),(3,'zhang');
INSERT INTO `t_posts` VALUES (1,'my posts 1',1,'my posts 1'),(2,'my posts 2',1,'lalala'),(3,'3 posts',1,'help me');
INSERT INTO `t_reply` VALUES (1,3,3,'reply the posts',0),(2,3,1,'reply to the first reply',1),(4,3,1,'i like the 1st floor',1),(5,3,2,'too the 2nd',2),(6,3,2,'love the posts',0),(7,3,3,'you got it',4);
希望的结果是，查询出这个帖子的回复，包含：回复人的id,name,回复的内容，回复给谁(id,name)，回复的那一楼，自己是那一楼，的这个结果集的列表(也就是bbs或者手机话题贴里那种查询出的结果，一列一列的展示出来)
页:
[1]