if object_id(N'article_comment') is not null
drop table article_comment;

if object_id(N'article') is not null
drop table article;

create table article(
 id int primary key,
 title nvarchar(200) not null,
 content nvarchar(max) not null
);

create table article_comment(
 id int identity primary key,
 content nvarchar(1000) not null,
 article_id int not null,
 add_date datetime not null
);

insert into article ( id, title, content )
select 1, 'title1', 'content1' union
select 2, 'title2', 'content2' union
select 3, 'title3', 'content3' union
select 4, 'title4', 'content4' union
select 5, 'title5', 'content5' union
select 6, 'title6', 'content6' union
select 7, 'title7', 'content7'

insert into article_comment ( content, article_id, add_date )
select 'comment1', 1, '2014-11-11' union all
select 'comment2', 1, '2014-11-12' union all
select 'comment3', 1, '2014-11-13' union all
select 'comment1', 2, '2014-11-14' union all
select 'comment1', 3, '2014-11-15' union all
select 'comment2', 3, '2014-11-16' union all
select 'comment3', 3, '2014-11-17' union all
select 'comment1', 4, '2014-11-18' union all
select 'comment2', 4, '2014-11-19' union all
select 'comment1', 5, '2014-11-20' union all
select 'comment2', 5, '2014-11-21' union all
select 'comment3', 5, '2014-11-22' union all
select 'comment5', 5, '2014-11-23' union all
select 'comment6', 5, '2014-11-24' union all
select 'comment7', 5, '2014-11-25' ;

select id, title, content,
  (select COUNT(*) from article_comment where a.id = article_id ) as comment_count,
  (select MAX( add_date ) from article_comment where a.id = article_id ) as max_comment_date
 from article as a ;
 

select id, title, content,
  b.comment_count,
  b.max_comment_date
 from article as a
  left join ( select COUNT(*) as comment_count,
        MAX( add_date ) as max_comment_date,
        article_id
        from article_comment
        group by article_id
   ) as b on a.id = b.article_id

select id, title, content, t.comment_count, t.max_comment_date
 from article as a outer apply (
  select COUNT(*) as comment_count,
      MAX( add_date ) as max_comment_date
   from article_comment as ac
   where a.id = ac.article_id
 ) as t;