[SQL2k5] cross apply, outer apply 예제 셈플 스크립트

/*******************************************************************************************
-- Title : [SQL2k5] cross apply, outer apply 예제 셈플 스크립트
-- Reference : dbrang.com
-- Key word : cross apply outer apply
*******************************************************************************************/
/*
** Tempdb에서
*/
use tempdb;
go
 
create table t1
( a int
, b varchar(5)
);
go
 
create table t2
( a int
, c varchar(5)
);
go

insert into t1 values (1,'1-1');
insert into t1 values (1,'1-2');
insert into t1 values (1,'1-3');
insert into t1 values (2,'2-1');
insert into t1 values (2,'2-2');
insert into t1 values (4,'4-1');
go
 
insert into t2 values (1,'111');
insert into t2 values (2,'222');
insert into t2 values (3,'333');
insert into t2 values (4,'444');
go
 
create function udf_apply(@a int)
returns table
as
return
( select *
  from t1
  where a = @a
);
go

select *
from t2 cross apply dbo.udf_apply(a);
go
 
select *
from t2 outer apply dbo.udf_apply(a);
go
 
select t2.a, min(b)
from t2 cross apply dbo.udf_apply(a)
group by t2.a;
go
 
select *
from t2 aa outer apply dbo.udf_apply(a) bb
where bb.a is null;
go
 
 
/*
** Northwind 에서
*/
use northwind;
go
 
create function udf_custorders
( @custid as nchar(5)
, @n as int
)
returns table
as
return
  select top (@n) * from orders
  where customerid = @custid
  order by orderdate desc, orderid desc;
go
 
-- customers 테이블에 있는 각 고객들에 대하여 가장 최근의 세 개 주문
-- (주문이 없는 고객들은 제외)
select c.customerid, c.companyname, o.*
from customers as c cross apply udf_custorders(c.customerid, 3) as o
order by c.customerid, o.orderdate desc, o.orderid desc;
go
 
-- customers 테이블에 있는 각 고객들에 대하여 가장 최근의 세 개 주문
-- (주문이 없는 고객들도 포함)
select c.customerid, c.companyname, o.*
from customers as c outer apply udf_custorders(c.customerid, 3) as o
order by c.customerid, o.orderdate desc, o.orderid desc
go