심심풀이 4.5번째

동일한 질문이 계속 반복되어서 급조한 심심풀이 입니다.

/*-------------------------------------------------------------------------*/
첫번째 : 동적쿼리에서 output을 받아내야 할경우

이문제는 거의 1주에 1번정도 질문이 올라오는것 같네요.

그냥 exec을 실행하시면 세션이 분리되기때문에 output을 받아낼수가 없고,
대신 sp_executesql 은 output을 사용할수 있습니다


구문
sp_executesql [@stmt =] stmt
[
     {, [@params =] N'@parameter_name  data_type [,...n]' }
     {, [@param1 =] 'value1' [,...n] }
]

-- 샘플1. output만 받을 경우
declare @sql nvarchar(1000)
declare @p nvarchar(100)                                  
declare @out int                --동적쿼리에서 나오는 output을 받기 위해서 선언
set @sql = 'select @cnt=count(*) from pubs..sales'
set @p = '@cnt int output'                     -- output 파라메터 선언
exec sp_executesql @sql,@p, @cnt=@out output   -- @cnt와 @out의 순서조심.

select @out

-----------
21

(1개 행 적용됨)

-- 샘플2. input만 있을경우
declare @sql nvarchar(1000)
declare @p nvarchar(100)                                  
declare @stor_id char(4)       --동적쿼리에다가 input을 주기 위해서 선언
set @sql = 'select * from pubs..sales where stor_id = @in'
set @p = '@in char(4)'                         -- input 파라메터 선언
set @stor_id = '6380'
exec sp_executesql @sql,@p, @in=@stor_id

stor_id ord_num     ord_date                    qty    payterms     title_id
------- ----------- --------------------------- ------ ------------ --------
6380    6871        1994-09-14 00:00:00.000     5      Net 60       BU1032
6380    722a        1994-09-13 00:00:00.000     3      Net 60       PS2091

(2개 행 적용됨)

-- 샘플3. input과 output 동시 사용
declare @sql nvarchar(1000)
declare @p nvarchar(100)
declare @out int               --동적쿼리에서 나오는 output을 받기 위해서 선언
declare @stor_id char(4)       --동적쿼리에다가 input을 주기 위해서 선언
set @sql = 'select @cnt=count(*) from pubs..sales where stor_id = @in'
set @p = '@in char(4), @cnt int output'    -- input, output 파라메터 동시 선언
set @stor_id = '6380'
exec sp_executesql @sql,@p, @in=@stor_id, @cnt=@out output

select @out

-----------
2

(1개 행 적용됨)

물론, BOL에 보시면 보다 자세한 설명 나와있습니다.

/*-------------------------------------------------------------------------*/
두번째 : outer join (이것두 아주 가끔씩 올라오는 질문이더군요)
inner join의 경우는 특별히 문제될것이 없고, 강좌란에 자세히 설명되어 있으므로 생략.

use pubs --많이 애용하는(?) pubs db사용
go

/*---------------------------------------------------*/
1. t-sql 조인(?) : *=, =*, 이것은 t-sql 조인이라기 보다는 ansi조인이 도입도기 전에
   ms에서 나름대로 outer join을 지원하기 위해 만들었었다는 전설속의 구문입니다.
   또한, 모든 where조건이 조인절로 해석 처리됩니다.
   조인후의 필터조건을 구현할수 없지요. 이것이 문제입니다.

ex)
select titles.title_id, sales.qty
from titles, sales
where titles.title_id *= sales.title_id
and sales.qty is null

title_id qty   
-------- ------
PC1035   NULL
PS1372   NULL
   :       :
BU2075   NULL

(18개 행 적용됨)

위 예를 보면 titles 테이블과 sales 테이블을 titles 테이블을 기준으로
outer join 하는데 먼저 titles 테이블의 내용을 보면

select title_id from titles
title_id
--------
PC1035
PS1372
  :
BU2075

(18개 행 적용됨)

이렇게 18개의 row가 있고, sales 테이블중 qty 컬럼이 null 인것을 보면

select qty from sales where qty is null

qty   
------

(0개 행 적용됨)

한건도 없지요.
그래서 위 샘플퀄리의 경우 titles테이블을 기준으로 sales테이블의 qty컬럼이
null인것이 없는것들을 left outer join 처리하니까
(즉, 위의 [ and sales.qty is null ] 이 구문이 조인절로 해석)

title_id qty   
-------- ------
PC1035   NULL
PS1372   NULL
   :       :
BU2075   NULL

(18개 행 적용됨)

이런식으로 결과가 나오게 되는것입니다.

인라인뷰로 묶어서 처리할려구 꽁수를 사용하여 보면
select title_id, qty
from (select titles.title_id, sales.qty
      from titles, sales
      where titles.title_id *= sales.title_id) x
where qty is null
그래두, sql 서버가 쿼리를 재해석하여 위와 동일하게 실행됩니다.

/*---------------------------------------------------*/
2. ansi 조인

select titles.title_id, sales.qty
from titles left outer join sales
on titles.title_id = sales.title_id
where qty is null

title_id qty   
-------- ------
PC9999   NULL
MC3026   NULL

(2개 행 적용됨)

이경우는 조인절과 where 절이 명확히 구분되죠.

on    : 조인전에 걸러질 조건
where : 조인후에 걸러질 조건


먼저 조인절을 보면

select titles.title_id, sales.qty
from titles left outer join sales
on titles.title_id = sales.title_id

title_id qty   
-------- ------
PC1035   30
PS1372   20
   :      :
PS2106   25
PC9999   NULL
TC3218   40
   :      :
MC3026   NULL
BU2075   35

(23개 행 적용됨)

이렇게 조인이 먼저 수행되고 그 결과로 나온 row set을 가지고, 그다음에
[ where qty is null ] 이 구문이 수행되면서 qty 컬럼이 null인것을 걸러내면

title_id qty   
-------- ------
PC9999   NULL
MC3026   NULL

(2개 행 적용됨)

이렇게 수행되는것입니다.

(*=, =*), (left outer join, right outer join)
이 둘의 차이는 명확합니다.

t-sql 조인 ansi 조인 이렇게 구분짓기보다는
조인이 작동하는 방식의 차이를 이해하시기 바랍니다.

그리고, (*=, =*) 이 구문의 경우 간혹 버그가 있기도 하고,
또, 차후버젼에서 계속지원이 될지 의문이구요.
ms가 예전부터 저 구문을 앞으로는 지원안한다고 했는데
아직까지는 지원이 되고 있는 상태입니다.

ANSI조인 구문을 사용할것을 강력히 권장합니다.
(물론, ms에서두 ansi구분을 사용할것을 권장하고 있네요)


/*-------------------------------------------------------------------------*/
그냥 이렇게 마무리 하자니 좀 허전해서 심심풀이 쿼리 하나더 ?
/*-----------------------------------------------------------*/

test_dt  test_amt         
-------- -----------------
20010101 1000
20010103 3000
20010109 9000
20010203 3000
20010228 28000
20010301 1000
20010311 11000
20010422 22000
20010429 28000

(9개 행 적용됨)

이와 같은 자료가 있는데 이것을 그대로 보여주면서 월별 소계와
이월금액(해당월까지의 누계) 을 보여주고 마지막에 전체 합계를 보여줄려면 ?
단, 위의 테이블을 단 한번만 읽고서 처리해야한다.
(심심풀이 4탄을 보신분이라면 쉽게 풀수 있으리라 생각합니다.)

<< 뽑고싶은 결과물 >>

일자                amt                                     
----------------- ----------------------------------------
20010101          1000
20010103          3000
20010109          9000
200101소계          13000
200101월이월금액       13000
20010203          3000
20010228          28000
200102소계          31000
200102월이월금액       44000
20010301          1000
20010311          11000
200103소계          12000
200103월이월금액       56000
20010422          22000
20010429          28000
200104소계          50000
합계                106000

(17개 행 적용됨)

/*-----------------------------------------------------------*/
set nocount on
create table  #test_01 (test_dt varchar(8), test_amt int)
go
insert into #test_01 values ('20010101', 1000 )
insert into #test_01 values ('20010103', 3000 )
insert into #test_01 values ('20010109', 9000 )
insert into #test_01 values ('20010203', 3000 )
insert into #test_01 values ('20010228', 28000 )
insert into #test_01 values ('20010301', 1000 )
insert into #test_01 values ('20010311', 11000 )
insert into #test_01 values ('20010422', 22000 )
insert into #test_01 values ('20010429', 28000 )
go
create table #dumy_no (no int)
go
declare @i int
set @i = 1
while (@i < 100)
begin
insert into #dumy_no values (@i)
set @i = @i + 1
end
set nocount off
/*-----------------------------------------------------------*/
select * from #test_01
select * from #dumy_no
/*-----------------------------------------------------------*/
select case when x.no=1 then 일자 else '20010'+convert(varchar(2),4-y.no)+'월이월금액' end 일자, sum(amt)
amt
from (
    select 일자, amt, sw, no
    from (
        select 일자, sw, sum(test_amt) amt
        from (
            select case when y.no=1 then test_dt
                        when y.no=2 then substring(test_dt,1,6)+'소계'
                        when y.no=3 then '합계' end 일자,
                    y.no sw,
                    test_amt
            from #test_01 x
                 cross join (select no from #dumy_no where no <= 3) y ) a
        group by 일자, sw) x
    cross join (select no from #dumy_no where no <= 2) y
    where y.no <= case when sw=2 then 2 else 1 end) x
cross join (select no from #dumy_no where no <= 4) y
where y.no <= case when x.no=1 then 1 else 4-convert(int,substring(x.일자,5,2)) end
group by case when x.no=1 then 일자 else '20010'+convert(varchar(2),4-y.no)+'월이월금액' end
order by 일자
/*-----------------------------------------------------------*/
역시나 귀찮아서 주석생략...

역시 급조한거라 좀 그렇네요.
다음번에는 좀더 재미있는 심심풀이를 만들어 봐야할텐데...

오타

[오타] 바루 위 더미테이블 만드는 부분 insert into #dumy_no values (1) -> insert into #dumy_no values (@i)로 수정해야됨다..