SQL Server中以apply运算符简化join和子查询



关系数据库中涉及多表操作会使查询变复杂。 多个表的查询，常用的操作方法就是使用表联接(join)或子查询。在sql server 2005中新增了apply运算符， 它能简化原来用join或子查询解决问题的方式。

   

示例， 系统有两个表， 一个文章表和一个文章评论表， 一对多的关系。

创建表结构代码

       

SQL code

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
);

             

插入测试数据

     

SQL code

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' ;

      

现在要通过查询得到文章表的全部字段加上文章的评论数和最新的评论日期。

               

第一种方案通过子查询来实现

     

SQL code

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 ;
 

  

这个查询中有两个字查询， 分别要查两次article_comment(文章评论) 表， 这是低效的做法

   

第二种方案通过联接(join)实现

     

SQL code

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

  

这种方案比上一种方案要好， 但显的过于复杂， 在联接查询中还嵌套了一层group by 聚合。

第三种方案能过apply运算符实现

   

SQL code

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;

    
    这种方案的执行计划与第二种相似， 但从查询复杂性来讲， 要比第二种更清晰， 至少少了一个group by 子句。 apply和join的区别在于， join所运算的左右两部份是先执行迪卡尔乘积（交叉联接）生成结果后再进行条件筛选， 而apply可以在执行迪卡尔乘积之前事先进行条件筛选并且还可以对结果集进行处理， 这样在某些需要运算而得到结果再进行合并的情况下会比较方便。在这个查询中使用了outer apply， 其实把outer apply换成 cross apply也同样可行， 因为在这个查询中无法体现outer apply和cross apply的区别。