sql查询:显示文章标题，发帖人、最后回复时间、对应回复人功能实现（代码教程）

数据表准备:

CREATE 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=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

数据准备:

INSERT 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 `articles` 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', '王五', '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 articles where parentid=a.id

解答:

select 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,找到它的最晚回复时间,再根据这个时间去匹配它的回复者

select * 
from 
  articles 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
where 
  a.parentid is null  and b.parentid=a.id   
order by a.id 

第二次写的SQL

思路:

1.查询一个回复表,从中筛选同一个帖子回复中最晚的时间

SELECT * 
FROM 
articles
 WHERE 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 null

3.两表关联查询 两表的关系是帖子表的id=回复表的parentid

解答:

select *
from
  articles as a,
(SELECT title as recoverText,postuser as recover,postdate as lastTime ,parentid
 FROM 
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 a.id 

十万级数据查询调优思路 :两表关联后查询的表作为虚表查询,进行分组

select *
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 null

2.得到的表依据姓名分组,统计姓名出现的次数,按照次数降序,第一条记录就是结果

或者将分组的表拿到,该表里面也有含有该帖子的id,关联帖子表可以得到详细信息

解答:

select *,count(a.postuser) 
from
 articles as a
where 
 a.parentid is null
group by a.postuser
order by count(a.postuser) desc

如果是回复最多的人呢,同理,只要把修改parentid is not null即可

二解:带有子查询的写法(效率低):这个时候情况就和求同一个帖子回复中最晚的时间的人的名字做法是一样的

select *,count(a.postuser) 
from
  articles as a
group by a.postuser
having 
count(a.postuser)= (select max(c.count) 
from
			(select  count(b.postuser) as count  
from
				 articles as b
				 where 
				 b.parentid is null group by b.postuser
) as c
			)
 and a.parentid is null