DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`version` int(11),
`name` varchar(80),
`createtime` timestamp not null default current_timestamp,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;insert into user (version,name) values (1,'ua');
insert into user (version,name) values (2,'ub');
insert into user (version,name) values (3,'uc');DROP TABLE IF EXISTS `posts`;
CREATE TABLE `posts` (
`id` int(11) NOT NULL auto_increment,
`uid` int(11),
`version` int(11),
`context` varchar(80),
`createtime` timestamp not null default current_timestamp,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into posts (uid,version,context) values (1,1,'pa1');
insert into posts (uid,version,context) values (1,2,'pb1');
insert into posts (uid,version,context) values (1,3,'pc1');
insert into posts (uid,version,context) values (2,4,'pa2');
insert into posts (uid,version,context) values (2,5,'pb2');
insert into posts (uid,version,context) values (2,6,'pc2');
insert into posts (uid,version,context) values (3,7,'pa3');
insert into posts (uid,version,context) values (3,8,'pb3');
insert into posts (uid,version,context) values (3,9,'pc3');DROP TABLE IF EXISTS `replies`;
CREATE TABLE `replies` (
`id` int(11) NOT NULL auto_increment,
`pid` int(11),
`version` int(11),
`comment` varchar(80),
`createtime` timestamp not null default current_timestamp,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;insert into replies (pid,version,comment) values (1,1,'ra1');
insert into replies (pid,version,comment) values (1,2,'rb1');
insert into replies (pid,version,comment) values (1,3,'rc1');insert into replies (pid,version,comment) values (2,4,'ra2');
insert into replies (pid,version,comment) values (2,5,'rb2');
insert into replies (pid,version,comment) values (2,6,'rc2');
insert into replies (pid,version,comment) values (3,7,'ra3');
insert into replies (pid,version,comment) values (3,8,'rb3');
insert into replies (pid,version,comment) values (3,9,'rc3');
insert into replies (pid,version,comment) values (4,10,'ra4');
insert into replies (pid,version,comment) values (4,11,'rb4');
insert into replies (pid,version,comment) values (4,12,'rc4');
insert into replies (pid,version,comment) values (5,13,'ra5');
insert into replies (pid,version,comment) values (5,14,'rb5');
insert into replies (pid,version,comment) values (5,15,'rc5');insert into replies (pid,version,comment) values (6,16,'ra6');
insert into replies (pid,version,comment) values (6,17,'rb6');
insert into replies (pid,version,comment) values (6,18,'rc6');insert into replies (pid,version,comment) values (7,19,'ra7');
insert into replies (pid,version,comment) values (7,20,'rb7');
insert into replies (pid,version,comment) values (7,21,'rc7');insert into replies (pid,version,comment) values (8,22,'ra8');
insert into replies (pid,version,comment) values (8,23,'rb8');
insert into replies (pid,version,comment) values (8,24,'rc8');insert into replies (pid,version,comment) values (9,25,'ra9');
insert into replies (pid,version,comment) values (9,26,'rb9');
insert into replies (pid,version,comment) values (9,27,'rc9');想要的得到的结果集!
得到每个用户最新的帖子和最新的回帖记录
+------+------+------+--------------+----------------+
| uid | pid | rid | postsversion | repliesversion |
+------+------+------+--------------+----------------+
| 1 | 3 | 9 | 3 | 9 |
| 2 | 6 | 18 | 6 | 18 |
| 3 | 9 | 27 | 9 | 27 |
+------+------+------+--------------+----------------+
| uid | pid | rid | postsversion | repliesversion |
+------+------+------+--------------+----------------+
| 1 | 3 | 9 | 3 | 9 |9、3、9怎么得到的
-> from user u ,posts p,replies r
-> where u.id=p.uid
-> and p.id=r.pid
-> and not exists (select 1 from posts where uid=p.uid and id>p.id)
-> and not exists (select 1 from replies where pid=r.pid and id>r.id);
+----+----+----+--------------+----------------+
| id | id | id | postsversion | repliesversion |
+----+----+----+--------------+----------------+
| 1 | 3 | 9 | 3 | 9 |
| 2 | 6 | 18 | 6 | 18 |
| 3 | 9 | 27 | 9 | 27 |
+----+----+----+--------------+----------------+
3 rows in set (0.00 sec)mysql>
'截至2012-05-31 10:35:24 总发帖:12 正常结帖:0 当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴?
http://community.csdn.net/Help/HelpCenter.htm#结帖