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或者手机话题贴里那种查询出的结果,一列一列的展示出来)
数据: 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); 根据提供的测试数据,产生的结果是什么样?
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
用户表:
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或者手机话题贴里那种查询出的结果,一列一列的展示出来)
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);
根据提供的测试数据,产生的结果是什么样?
楼主能直接用数据例子来描述一下结果吗? 不要用文字描述了。 参考一下 http://bbs.csdn.net/topics/320211382 中它对结果的要求描述。
楼主能直接用数据例子来描述一下结果吗? 不要用文字描述了。 参考一下 http://bbs.csdn.net/topics/320211382 中它对结果的要求描述。
结果:
回复标识/帖子标识/ 用户标识/用户名/回复内容/楼层/回复给谁(引用的回复标识)/回复给谁(引用的用户标识)/回复给谁(用户名)/回复给几楼的
id, posts_id, user_id, user_name, content, floor,rid,r_user_id,r_user_name,r_floor
1 3 3 "zhang' 'reply the posts' 1 0 1 小li 0
2 3 1 小li reply to the first reply 2 1 3 zhang 1
4 3 1 小li i like the 1st floor 3 1 3 zhang 1
5 3 2 xiaowang too the 2nd 4 2 1 小li 2
6 3 2 xiaowang love the posts 5 0 1 小li 0
7 3 3 zhang you got it 6 4 1 小li 3
每一个元组就是上面的内容了
1 3 3 "zhang' 'reply the posts' 1 0 1 小li 0
2 3 1 小li reply to the first reply 2 1 3 zhang 1
4 3 1 小li i like the 1st floor 3 1 3 zhang 1
5 3 2 xiaowang too the 2nd 4 2 1 小li 2
6 3 2 xiaowang love the posts 5 0 1 小li 0
7 3 3 zhang you got it 6 4 1 小li 3
--------->>红色部分是怎么逻辑产生的?
+----+----------+
| id | name |
+----+----------+
| 1 | 小li |
| 2 | xiaowang |
| 3 | zhang |
+----+----------+
3 rows in set (0.00 sec)mysql> select * from t_posts;
+----+------------+--------+------------+
| id | title | author | content |
+----+------------+--------+------------+
| 1 | my posts 1 | 1 | my posts 1 |
| 2 | my posts 2 | 1 | lalala |
| 3 | 3 posts | 1 | help me |
+----+------------+--------+------------+
3 rows in set (0.00 sec)mysql> select * from t_reply;
+----+----------+------+--------------------------+-----+
| id | posts_id | user | content | rid |
+----+----------+------+--------------------------+-----+
| 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 |
+----+----------+------+--------------------------+-----+
6 rows in set (0.00 sec)mysql> select id, p......
+----+----------+---------+-----------+--------------------------+-------+-----+-----------+-------------+---------+
| id | posts_id | user_id | user_name | content | floor | rid | r_user_id | r_user_name | r_floor |
+----+----------+---------+-----------+--------------------------+-------+-----+-----------+-------------+---------+
| 1 | 3 | 3 | zhang | reply the posts | 1 | 0 | NULL | NULL | 0 |
| 2 | 3 | 1 | 小li | reply to the first reply | 2 | 1 | 3 | zhang | 1 |
| 4 | 3 | 1 | 小li | i like the 1st floor | 3 | 1 | 3 | zhang | 1 |
| 5 | 3 | 2 | xiaowang | too the 2nd | 4 | 2 | 1 | 小li | 2 |
| 6 | 3 | 2 | xiaowang | love the posts | 5 | 0 | NULL | NULL | 0 |
| 7 | 3 | 3 | zhang | you got it | 6 | 4 | 1 | 小li | 3 |
+----+----------+---------+-----------+--------------------------+-------+-----+-----------+-------------+---------+
6 rows in set (0.01 sec)
那个是楼主的id和名称,需要做特殊处理吗?还是这个为null就表示回复给楼主的
这个逻辑上的东西,需要您自己决定。
或者详细说明一下 红色部分是通过什么逻辑出来的? 为什么不是 3 zhang ?1 3 3 "zhang' 'reply the posts' 1 0 1 小li 0
这个逻辑上的东西,需要您自己决定。
或者详细说明一下 红色部分是通过什么逻辑出来的? 为什么不是 3 zhang ?1 3 3 "zhang' 'reply the posts' 1 0 1 小li 0
因为如果rid=0标识的是回复主贴的内容,所以只要是rid=0那么这个r_user_id 和r_user_name是标识发帖人的。
这个逻辑上的东西,需要您自己决定。
或者详细说明一下 红色部分是通过什么逻辑出来的? 为什么不是 3 zhang ?1 3 3 "zhang' 'reply the posts' 1 0 1 小li 0
另外,这个posts_id=3表示帖子的标识,id=3的帖子author=1就表示他的user id = 1 ,所以从user表查到他叫小li
+----+----------+
| id | name |
+----+----------+
| 1 | 小li |
| 2 | xiaowang |
| 3 | zhang |
+----+----------+
3 rows in set (0.00 sec)mysql> select * from t_posts;
+----+------------+--------+------------+
| id | title | author | content |
+----+------------+--------+------------+
| 1 | my posts 1 | 1 | my posts 1 |
| 2 | my posts 2 | 1 | lalala |
| 3 | 3 posts | 1 | help me |
+----+------------+--------+------------+
3 rows in set (0.00 sec)mysql> select * from t_reply;
+----+----------+------+--------------------------+-----+
| id | posts_id | user | content | rid |
+----+----------+------+--------------------------+-----+
| 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 |
+----+----------+------+--------------------------+-----+
6 rows in set (0.00 sec)mysql> select id, posts_id, user as user_id, (select name from t_user where id=r.user) as user_name,
-> content,
-> (select count(*) from t_reply where posts_id=r.posts_id and id<=r.id) as floor,
-> rid,
-> if(rid=0,(select user from t_posts where id=r.posts_id),(select user from t_reply where id=r.rid)) as r_user_id,
-> if(rid=0,
-> (select name from t_posts,t_user where t_posts.author= t_user.id and t_posts.id=r.posts_id),
-> (select name from t_reply,t_user where t_reply.user=t_user.id and t_reply.id=r.rid)
-> ) as r_user_name,
-> (select count(*) from t_reply where posts_id=r.posts_id and id<=r.rid) as r_floor
-> from t_reply r;
+----+----------+---------+-----------+--------------------------+-------+-----+-----------+-------------+---------+
| id | posts_id | user_id | user_name | content | floor | rid | r_user_id | r_user_name | r_floor |
+----+----------+---------+-----------+--------------------------+-------+-----+-----------+-------------+---------+
| 1 | 3 | 3 | zhang | reply the posts | 1 | 0 | 3 | 小li | 0 |
| 2 | 3 | 1 | 小li | reply to the first reply | 2 | 1 | 3 | zhang | 1 |
| 4 | 3 | 1 | 小li | i like the 1st floor | 3 | 1 | 3 | zhang | 1 |
| 5 | 3 | 2 | xiaowang | too the 2nd | 4 | 2 | 1 | 小li | 2 |
| 6 | 3 | 2 | xiaowang | love the posts | 5 | 0 | 2 | 小li | 0 |
| 7 | 3 | 3 | zhang | you got it | 6 | 4 | 1 | 小li | 3 |
+----+----------+---------+-----------+--------------------------+-------+-----+-----------+-------------+---------+
6 rows in set (0.01 sec)mysql>