《sql查询显示文章标题》由会员分享，可在线阅读，更多相关《sql查询显示文章标题（4页珍藏版）》请在金锄头文库上搜索。
1、sqlsql 查询查询: :显示文章标题，发帖人、最后回复时间、对应回复显示文章标题，发帖人、最后回复时间、对应回复人功能实现人功能实现数据表准备:? 1 2 3 4 5 6 7 8CREATE TABLE articles (id int(11) NOT NULL AUTO_INCREMENT,title varchar(50) DEFAULT NULL,postuser varchar(10) DEFAULT NULL,postdate datetime DEFAULT NULL,parentid int(11) DEFAULT NULL,PRIMARY KEY (id) ) ENGINE
2、=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;数据准备:? 1 2 3 4 5 6 7 8 9 10 11INSERT INTO articles VALUES (1, 第一条帖子, 张三, 1998-10-10 12:32:32, null); INSERT INTO articles VALUES (2, 第二条帖子, 张三, 1998-10-10 12:34:32, null);INSERT INTO articles VALUES (3, 第一条回复 1, 李四, 1998-10-10 12:35:32, 1);INSERT INTO a
3、rticles VALUES (4, 第二条回复 1, 李四, 1998-10-10 12:36:32, 2);INSERT INTO articles VALUES (5, 第一条回复 2, 王五, 1998-10-10 12:37:32, 1);INSERT INTO articles VALUES (6, 第一条回复 3, 李四, 1998-10-10 12:38:32, 1);INSERT INTO articles VALUES (7, 第二条回复 2, 李四, 1998-10-10 12:39:32, 2);INSERT INTO articles VALUES (8, 第一条回复
4、 4, 王五, 1998-10-10 12:39:40, 1);INSERT INTO articles VALUES (9, 第三条帖子, inleft, 2018-06- 21 17:13:53, null);INSERT INTO articles VALUES (10, 第四条帖子, inleft, 2018-06- 14 17:13:57, null);INSERT INTO articles VALUES (11, 第六条帖子, inleft, 2018-06- 20 17:14:18, null);必要条件,最后回复时间:select max(postdate) from art
5、icles where parentid=a.id解答:? 1 2 3 4select a.title,a.postuser,a.postdate as firstTime,(select max(postdate) from articles where parentid=a.id ) lastTime from articles as a where a.parentid is null深入,需要把所有帖子的 最后的回复人和时间都列举出来第一次写的 SQL，缺点，只能查询单个帖子的数据思路:查询某个帖子的 id,根据这个 id,找到它的最晚回复时间,再根据这个时间去匹配它的回复者? 1 2
6、 3 4 5 6 7 8 9 10 11 12 13 14 15select * fromarticles as a,( select * from articles where postdate in (select max(postdate) from articles where parentid in(select id from articles where parentid is “帖子的 id“) as b wherea.parentid is null and b.parentid=a.id order by a.id第二次写的 SQL思路:1.查询一个回复表,从中筛选同一个帖
7、子回复中最晚的时间? 1 2 3 4SELECT * FROM articlesWHERE parentid is not null and postdate IN ( SELECT max(postdate) FROM articles b WHERE parentid =articles.parentid )2.查询一个帖子表select *from articles as a where a.parentid is null3.两表关联查询 两表的关系是帖子表的 id=回复表的 parentid解答:来运棋牌 http:/? 1 2 3 4 5 6 7 8 9 10 11select *
8、 fromarticles as a, (SELECT title as recoverText,postuser as recover,postdate as lastTime ,parentidFROM articles WHERE parentid is not null and postdate IN ( SELECT max(postdate) FROM articles temp WHERE temp.parentid =articles.parentid ) ) as b where a.parentid is null and b.parentid =a.id order by
9、 a.id 十万级数据查询调优思路 :两表关联后查询的表作为虚表查询,进行分组? 1 2 3 4 5 6 7 8 9 10select * from(select b.*,a.title as a_title from articles as a,articles as b where a.parentid is null and b.parentid=a.id order by b.postdate desc ) as temp group by parentid题目:求出发帖最多的人：1.查询一个帖子表select *fromarticles as awhere a.parentid is
10、 null2.得到的表依据姓名分组,统计姓名出现的次数,按照次数降序,第一条记录就是结果或者将分组的表拿到,该表里面也有含有该帖子的 id,关联帖子表可以得到详细信息解答:? 1 2select *,count(a.postuser) from3 4 5 6 7articles as a wherea.parentid is null group by a.postuser order by count(a.postuser) desc如果是回复最多的人呢,同理,只要把修改 parentid is not null 即可二解:带有子查询的写法(效率低):这个时候情况就和求同一个帖子回复中最晚的
11、时间的人的名字做法是一样的棋牌评测网 http:/? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15select *,count(a.postuser) fromarticles as a group by a.postuser having count(a.postuser)= (select max(c.count) from(select count(b.postuser) as count fromarticles as bwhereb.parentid is null group by b.postuser ) as c)and a.parentid is null
