database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Tutorials 507 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 507
T-SQL의 해결사 - CASE 함수
작성자
정재익(advance)
작성일
2002-08-29 16:39:51
조회수
27,235

T-SQL의 해결사 - CASE 함수

 

원본출처 : http://www.zdnet.co.kr/develop/backend/db/article.jsp?id=48021&page=3&forum=0

 

김정선 (데브피아)

2002/04/13

 

 

SQL Server에 관련된 Q/A란을 접하다 보면, CASE 함수를 몰라서 난처해하는 경우를 자주 보게 된다. 특히 오라클 개발자들의 경우 DECODE문과 같은 기능을 수행하는 CASE 함수를 몰라서 곧잘 질문하는 것을 볼 수 있다.

 

이제 그런 고민을 접자. CASE 함수는 T-SQL의 해결사 중 하나이다. 단순하지만 강력한 위력을 발휘하는 CASE 함수를 이번 기회에 알아두도록 하자.

 

SQL Server에서 Transact-SQL, Batch, Stored Procedure, Function, Trigger 프로그램을 하면서 조건문이 필요한 경우 if-else 문을 사용할 수 있다. 하지만 if-else문은 표준 SQL구문이 아니다. 따라서 SELECT 구문 같은 DML 구문 내에서 사용하지 못한다. 이때 CASE 함수를 적용할 수 있다.

 

더욱이 CASE 함수는 말 그대로 함수이기 때문에 함수가 사용될 수 있는 곳이라면 CASE문을 쓸 수 있다.

 

참고로 아래 예제 코드들은 텍스트로 보기에는 불편한 점이 있기 때문에 쿼리 분석기에 복사해서 테스트하기 바란다.

 

DECODE 보다 강력한 함수

CASE 함수는 다중 if문과 같은 구조를 가진다. 더불어 SELECT 구문 같은 DML과 연동을 해서 행 단위의 조건 처리가 가능하다는 특징을 가지고 있기 때문에 복잡한 쿼리 작성 시에 필수적으로 사용된다. 오라클에는 일반적으로 DECODE 라는 명령을 통해 = 비교에 따른 행 단위 조건 처리가 가능했었지만 SQL Server에서는 ANSI 표준이며 보다 더 강력한 형식의 CASE 함수를 사용한다 (물론 오라클에서도 된다).

 

구문

CASE 함수는 지정된 조건식을 평가하고, 조건에 만족하는 결과 중 하나를 리턴한다.

CASE 함수는 다음과 같은 항목들로 구성된다.

 

CASE 키워드

변환할 열 이름

검색할 식을 지정하는 WHEN절 및 이를 바꿀 식을 지정하는 THEN 절

END 키워드

CASE 함수의 별칭을 정의하는 선택적 AS 절

 

 

또한 두 가지 형식으로 사용될 수 있다.

 

1. 단순 CASE 함수

 

조건식이 한 번만 지정되고 WHEN절에는 조건식과 비교할 상수값만 선언한다. 각각의 WHEN절을 비교해 조건에 만족하면 THEN절 다음의 구문이 실행된다.

 

syntax 1.

CASE input_expression

WHEN when_expression1 THEN result_expression1

WHEN when_expression2 THEN result_expression2

[ ...n ]

[ ELSE else_result_expression ]

END

 

 

2. 검색된 CASE 함수 (CASE 절에는 아무런 조건식도 오지 않는다)

 

각 WHEN절에 필요한 조건식을 별도로 지정할 수 있다.조건이 만족하면 THEN절 다음에 명령이 실행된다.

 

syntax 2.

CASE

WHEN Boolean_expression1 THEN result_expression1

WHEN Boolean_expression2 THEN result_expression2

[ ...n ]

[ ELSE else_result_expression ]

END

 

NOTE: THEN절 다음에는 일반 상수값 뿐만 아니라 함수나 심지어 또 다른 쿼리문이 올 수도 있다. 반드시 잊지 말 것은 result_expression은 반드시 하나의 값을 리턴해야 한다는 것이다. 하지만 행 단위로 각각의 검색 조건이 반복 실행되므로, 성능에 관한한 최상의 결과를 얻기는 힘들다.

 

/* 예제 1. CASE 함수 형식 별 예제 */

 

-- 1. 단순 CASE

declare @i int, @j varchar(10)

set @i = 1

set @j = (case @i when 1 then 'true'

when 2

then 'false'

else 'else' end)

select @j

-- 'true'가 출력

 

 

-- 2. 검색된 CASE

set @i = 5

set @j = (case when @i <= 1 then 'true'

when @i = 5

then 'false'

else 'else' end)

select @j

-- 'false'가 출력

 

-- 3. 서브쿼리를 사용한 예

set @j = (case 1 when 1

then (select count

(*) from northwind..products)

end)

select @j

-- 'false'가 출력

 

-- 4. 함수로 사용한 예, 계산된 컬럼과 연동

create table tempdb.dbo.casetable (

grade char(1)

, msg AS (case grade when 'a' then '최고' when 'b' then '우

수' else '보통' end)

)

insert into tempdb.dbo.casetable

select 'a' union all select 'b' union all select 'c'

 

select * from tempdb.dbo.casetable

 

CASE를 사용한 조건 데이터 처리

실제로 CASE문을 필요로 하는 것은 바로 DML 명령들이다. select 구문을 처리되는 집합 중 특정 컬럼값에 기초한 조건을 기준으로 테이블에는 없지만 새로운 집합에 포함돼야 할 컬럼을 새로 생성하고, 그 컬럼값을 추출, 가공, 연산하는 용도로 곧잘 사용된다.

 

즉 select되는 각각의 행(row, record)에 대해 조건을 부여하고 조건에 만족하는 결과를 새로운 컬럼값에 할당하는 것이다.

 

이제, CASE문을 조건 데이터 처리와 함께 사용한 경우에 두 가지 특징을 보게 될 것이다.

 

CASE의 특징

1. 행 단위의 조건 처리가 가능

2. 새로운 컬럼 생성

 

아래 예제들을 통해 이러한 사실을 이해할 수 있을 것이다. 참고로 관계형 데이터를 집합으로서 접근하다 보면 두 가지의 처리 방법을 요구하는 경우가 곧잘 발생한다. 행->열, 열->행으로 각각 변경하는 작업이다. 이중 행->열로 변경하는 작업에 필요한 것이 바로 CASE문이다.

 

USE Northwind

 

-- 상황 1

-- 상황 1-1. 우선, 아래 데이터를 보자

select top 3 productid, unitsinstock from dbo.products

 

/* 결과

productid unitsinstock

----------- ------------

1 39

2 17

3 13

4 53

......

*/

 

-- 상황 1-2. 위에서, 각 제품코드별로 unitsinstock의 값이

-- 20보다 작거나 값으면 '재고 없슴'

-- 50보다 작으면 '주문 요망'

-- 그렇치 않으면 '재고' 라는 새로운 컬럼을 요구한다.

 

/* 결과

productid unitsinstock state

----------- ------------ ---------

1 39 주문 요망

2 17 재고 없슴

3 13 재고 없슴

4 53 재고

......

*/

 

-- 상황 1-3. 아래와 같이, 간단하게 해결할 수 있다.

select productid, unitsinstock,

(case

when unitsinstock <= 20 then '재고 없슴'

when unitsinstock < 50 then '주문 요망'

else '재고'

end) state

from dbo.products

GO

 

-- 상황 2

-- 상황 2-1. 우선, 아래 데이터를 보자

USE Northwind

 

select productid, year(orderdate) as orderdate, quantity

from dbo.[order details] od join dbo.orders o

on od.orderid = o.orderid

order by productid

 

/* 결과

productid orderdate quantity

----------- ----------- --------

1 1996 45

1 1996 18

1 1996 20

1 1996 15

1 1996 12

......

*/

 

-- 상황 2-2. 위에서, 각 제품코드별로 년도별 주문수량의 합계를

-- 구하고자 한다면?

 

/* 결과

productid y1996 y1997 y1998

----------- ----------- ----------- -----------

1 125 304 399

2 226 435 396

3 30 190 108

4 107 264 82

......

*/

 

 

T-SQL의 해결사 - CASE 함수

 

 

 

-- 상황 2-3. 아래와 같이, 간단하게 해결할 수 있다.

USE Northwind

 

select productid

, sum(case when year(orderdate) <= 1996 then quantity end) as

y1996

, sum(case when year(orderdate) = 1997 then quantity end) as

y1997

, sum(case when year(orderdate) >= 1998 then quantity end) as

y1998

from dbo.[order details] od join dbo.orders o

on od.orderid = o.orderid

group by productid

order by productid

 

/*

select productid

, sum(case when odate <= 1996 then quantity end) as y1996

, sum(case when odate = 1997 then quantity end) as y1997

, sum(case when odate >= 1998 then quantity end) as y1998

from (select orderid, sum(year(orderdate)) odate

from orders group by orderid) o inner merge join [order details] od

on o.orderid = od.orderid

group by productid

order by productid

*/

 

-- 상황 3

-- 상황 3-1. 우선, 아래 데이터를 보자

USE Pubs

 

select pub_name, state from dbo.publishers

order by state asc

/* 결과

pub_name state

---------------------------------------- -----

GGG&G NULL

Lucerne Publishing NULL

Algodata Infosystems CA

Binnet & Hardley DC

Five Lakes Publishing IL

......

*/

 

-- 상황 3-2. 위에서, state 컬럼을 오름차순으로 출력하되

-- NULL 값은 맨 뒤에 나와야 한다면?

 

/* 결과

pub_name state

---------------------------------------- -----

Algodata Infosystems CA

Binnet & Hardley DC

Five Lakes Publishing IL

New Moon Books MA

Scootney Books NY

Ramona Publishers TX

GGG&G NULL

Lucerne Publishing NULL

*/

 

-- 상황 2-3. 아래와 같이, 간단하게 해결할 수 있다.

select pub_name, state from pubs.dbo.publishers

order by (case when state is null then 1

else 0 end), state

 

/*

 

CASE 함수는 다양한 상황의 문제 해결에 활용될 수 있다. CASE 함수의 적용 예를 통해 여러분의 T-SQL 프로그램 능력이 향상될 수 있을 것이다. 참고로 T-SQL에는 CASE와 유사한 COALESCE() 함수가 있다. @

[Top]
No.
제목
작성자
작성일
조회
2154Microsoft Access functions (Vs) SQL Server functions
이경환
2005-07-28
13443
2027MS-Access Jet database DB guide
이창민
2005-06-14
16863
857Temporary 테이블 vs. Table 데이터 타입
정재익
2003-06-19
19699
507T-SQL의 해결사 - CASE 함수
정재익
2002-08-29
27235
506동적 쿼리의 해결사 sp_executesql vs. exec
정재익
2002-08-29
18452
504DB 파일을 D드라이브로 옮기는 방법
정재익
2002-08-29
11955
453SQL Server FAQ (4)
정재익
2002-07-17
41234
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2019 DSN, All rights reserved.
작업시간: 0.072초, 이곳 서비스는
	PostgreSQL v11.5로 자료를 관리합니다