天天看點

查詢文章的最後回複時間

表:id,title,postuser,postdate,parentid

                  準備sql語句:

                  drop table if exists articles;

                  create table articles(id int auto_increment primary key,title

                  varchar(50), postuser varchar(10), postdate datetime,parentid

                  int references articles(id));

                  insert into articles values

                  (null,'第一條','張三','1998-10-10 12:32:32',null),

                  (null,'第二條','張三','1998-10-10 12:34:32',null),

                  (null,'第一條回複1','李四','1998-10-10 12:35:32',1),

                  (null,'第二條回複1','李四','1998-10-10 12:36:32',2),

                  (null,'第一條回複2','王五','1998-10-10 12:37:32',1),

                  (null,'第一條回複3','李四','1998-10-10 12:38:32',1),

                  (null,'第二條回複2','李四','1998-10-10 12:39:32',2),

                  (null,'第一條回複4','王五','1998-10-10 12:39:40',1);

                  答案:

                  select a.title,a.postuser,(select max(postdate) from articles where parentid=a.id) reply from articles a where a.parentid is null;