在论坛中寻找发帖网,主要是考察函数count和max的应用,共用了两张表users和posts,
建表语句并插入数据
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(20) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert INTO users(name) values("tom");
insert INTO users(name) values("jerry");
insert INTO users(name) values("text");
insert INTO users(name) values("wang");
insert INTO users(name) values("zhou");
insert INTO users(name) values("liu");
insert INTO users(name) values("sun");
CREATE TABLE `posts` (
`id` int(11) NOT NULL auto_increment,
`uid` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert INTO posts(uid) values(1);
insert INTO posts(uid) values(1);
insert INTO posts(uid) values(1);
insert INTO posts(uid) values(1);
insert INTO posts(uid) values(1);
insert INTO posts(uid) values(2);
insert INTO posts(uid) values(2);
insert INTO posts(uid) values(2);
insert INTO posts(uid) values(2);
insert INTO posts(uid) values(2);
insert INTO posts(uid) values(2);
insert INTO posts(uid) values(3);
insert INTO posts(uid) values(4);
insert INTO posts(uid) values(4);
insert INTO posts(uid) values(4);
insert INTO posts(uid) values(5);
insert INTO posts(uid) values(5);
insert INTO posts(uid) values(5);
insert INTO posts(uid) values(6);
insert INTO posts(uid) values(6);
insert INTO posts(uid) values(7);
查找发帖王
select name , p_c.max_count as max_count from users as u,
(select COUNT(uid) as count_uid,uid from posts group by uid) as p,
(select MAX(post_count.count_uid) as max_count from
(select COUNT(uid) count_uid,uid from posts group by uid) as post_count) as p_c
where p.count_uid = p_c.max_count
and p.uid = u.id
建表语句并插入数据
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(20) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert INTO users(name) values("tom");
insert INTO users(name) values("jerry");
insert INTO users(name) values("text");
insert INTO users(name) values("wang");
insert INTO users(name) values("zhou");
insert INTO users(name) values("liu");
insert INTO users(name) values("sun");
CREATE TABLE `posts` (
`id` int(11) NOT NULL auto_increment,
`uid` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert INTO posts(uid) values(1);
insert INTO posts(uid) values(1);
insert INTO posts(uid) values(1);
insert INTO posts(uid) values(1);
insert INTO posts(uid) values(1);
insert INTO posts(uid) values(2);
insert INTO posts(uid) values(2);
insert INTO posts(uid) values(2);
insert INTO posts(uid) values(2);
insert INTO posts(uid) values(2);
insert INTO posts(uid) values(2);
insert INTO posts(uid) values(3);
insert INTO posts(uid) values(4);
insert INTO posts(uid) values(4);
insert INTO posts(uid) values(4);
insert INTO posts(uid) values(5);
insert INTO posts(uid) values(5);
insert INTO posts(uid) values(5);
insert INTO posts(uid) values(6);
insert INTO posts(uid) values(6);
insert INTO posts(uid) values(7);
查找发帖王
select name , p_c.max_count as max_count from users as u,
(select COUNT(uid) as count_uid,uid from posts group by uid) as p,
(select MAX(post_count.count_uid) as max_count from
(select COUNT(uid) count_uid,uid from posts group by uid) as post_count) as p_c
where p.count_uid = p_c.max_count
and p.uid = u.id