SQL문 실습 - CASE문(1)

- SQL문 실습 - CASE문(1) -

CASE문을 사용하면 SQL문에서 IF문처럼 사용할 수 있습니다.

이제부터 CASE문 예제를 보면서 CASE문에 대해서 알아보겠습니다.

1. 

SELECT T1.CustomerID
      ,CASE ContactTitle
            WHEN 'Accounting Manager'                THEN 'ACC_MNG'
            WHEN 'Assistant Sales Agent'            THEN 'ASS_SAL_AGE'
            WHEN 'Assistant Sales Representative'    THEN 'ASS_SAL_REP'
            WHEN 'Marketing Assistant'                THEN 'MKT_ASS'
            WHEN 'Markeing Manager'                    THEN 'MKT_MNG'
            WHEN 'Order Administrator'                THEN 'ORD_ADM'
            WHEN 'Owner'                            THEN 'OWNER'
            WHEN 'Owner/Marketing Assistant'        THEN 'OWNER_MKT_ASS'
            WHEN 'Sales Agent'                        THEN 'SAL_AGE'
            WHEN 'Sales Associate'                    THEN 'SAL_ASO'
            WHEN 'Sales Manager'                    THEN 'SAL_MNG'
            WHEN 'Sales Representative'                THEN 'SAL_REP'
            END ContactTitle
   FROM Customers T1

CASE문을 이용해서 'ContactTitle'이 'Accounting Manager', 'Assistant Sales Agent' 등에 해당하는 값일 경우 'ACC_MNG', 'ASS_SAL_AGE'등으로 값을 요약해서 표현할 수 있습니다.

- 결과 -

2.

--  Use Orders table
--    Select OrderDate in 1996-07
--    Select CustomerID, EmployeeID, Freight, Freight_Sale, SaleType columns
--    Freight_Sale = IF EmployeeID is 3 then Freight * 0.8 else Freight
--    SaleType = If EmployeeID is 3 then 'Sale Freight' Else ''
------------------------------------------------------------------------------
SELECT T1.CustomerID
      ,T1.EmployeeID
      ,T1.OrderDate
      ,T1.Freight
      ,CASE T1.EmployeeID 
            WHEN 3 
            THEN T1.Freight * 0.8 
            ELSE T1.Freight END Freight_Sale
      ,CASE T1.EmployeeID    
            WHEN 3 
            THEN 'Sale Freight' 
            ELSE '' END SaleType
  FROM Orders T1
 WHERE T1.OrderDate >= '1996-07-01' AND T1.OrderDate < '1996-08-01'

CASE문을 이용해서 T1.EmployeeID가 3인 경우 T1.Freight * 0.8을 합니다.

CASE문을 이용해서 T1.EmployeeID가 3인 경우 Sale Freight를 붙여주는 SaleType을 합니다.

 SELECT T1.CustomerID
      ,T1.EmployeeID
      ,T1.OrderDate
      ,T1.Freight
      ,CASE WHEN T1.EmployeeID = 3 
            THEN T1.Freight * 0.8 
            ELSE T1.Freight END Freight_Sale
      ,CASE WHEN T1.EmployeeID = 3 
            THEN 'Sale Freight' 
            ELSE '' END SaleType
  FROM Orders T1
 WHERE T1.OrderDate >= '1996-07-01' AND T1.OrderDate < '1996-08-01'

이렇게 CASE문을 사용해도 같은 결과가 나옵니다.

- 결과 -

3. 

--  Use Orders table
--    Select OrderDate in 1996-07
--    Select CustomerID, EmployeeID, OrderDate, ShippedDate, Freight, ShipDayCount, Freight_Sale, SaleType     
--    ShipDayCount = Different between OrderDate and ShippedDate
--    Freight_Sale = If ShipDayCount bigger or equal 10days then Freight * 0.9 else Freight
--    SaleType = If ShipDayCount bigger or equal 10day then "Sale Freight" else ''
------------------------------------------------------------------------------
SELECT T1.CustomerID
      ,T1.EmployeeID
      ,T1.OrderDate
      ,T1.ShippedDate
      ,T1.Freight
      ,DATEDIFF(DAY, T1.OrderDate, T1.ShippedDate) ShipDayCount
      ,CASE WHEN DATEDIFF(DAY, T1.OrderDate, T1.ShippedDate) >= 10 
            THEN T1.Freight * 90 / 100 
            ELSE T1.Freight END Freight_Sale
      ,CASE WHEN DATEDIFF(DAY, T1.OrderDate, T1.ShippedDate) >= 10 
            THEN 'Sale Freight' 
            ELSE '' END SaleType
  FROM Orders T1
 WHERE T1.OrderDate >= '1996-07-01' AND T1.OrderDate < '1996-08-01'

위와 같은 CASE문이지만 좀 더 조건을 복잡하게 걸어서도 사용할 수 있습니다.

- 결과 -

4. 

--  Use Orders table
--    Select OrderDate Between '1996-07' and '1996-12'
--    Select Freight_07, Freight_08 ~ Freight_12 column
--    Freight_07 = Sum of Freight about 1996-07 sales
--    Freight_08 = Sum of Freight about 1996-08 sales
--    ..
--    Freight_12 = Sum of Freight about 1996-12 sales
------------------------------------------------------------------------------
SELECT SUM(CASE WHEN DATEPART(MONTH, T1.OrderDate) = 7 THEN T1.Freight ELSE 0 END) Freight_07
      ,SUM(CASE WHEN DATEPART(MONTH, T1.OrderDate) = 8 THEN T1.Freight ELSE 0 END) Freight_08
      ,SUM(CASE WHEN DATEPART(MONTH, T1.OrderDate) = 9 THEN T1.Freight ELSE 0 END) Freight_09
      ,SUM(CASE WHEN DATEPART(MONTH, T1.OrderDate) = 10 THEN T1.Freight ELSE 0 END) Freight_10
      ,SUM(CASE WHEN DATEPART(MONTH, T1.OrderDate) = 11 THEN T1.Freight ELSE 0 END) Freight_11
      ,SUM(CASE WHEN DATEPART(MONTH, T1.OrderDate) = 12 THEN T1.Freight ELSE 0 END) Freight_12
  FROM Orders T1
 WHERE T1.OrderDate BETWEEN '1996-07-01' AND '1996-12-31'

CASE문 안에 DATEPART를 이용해서 월별로 나눠줍니다.

그리고 CASE문을 집계함수 SUM을 이용해서 Freight의 합을 구하는 CASE문입니다.

- 결과 -

5. 

--  Use Orders table
--    Select OrderDate between 1996-07 and 1996-12
--    Select only OrderID, CustomerID, EmployeeID, Freight, SaleFreight, SaleType columns
--    SaleFreight = If CustomerID is VINET or EmployeeID is 5 then Freight * 0.9 else Freight
--    SaleType = If CustomerID is VINET or EmployeeID is 5 then "Sale Freight" else ''
------------------------------------------------------------------------------
SELECT T1.OrderID
      ,T1.CustomerID
      ,T1.EmployeeID
      ,T1.Freight
      ,CASE WHEN T1.CustomerID = 'VINET' OR T1.EmployeeID = 5 
            THEN T1.Freight * 90/100 
            ELSE T1.Freight END SaleFreight
      ,CASE WHEN T1.CustomerID = 'VINET' OR T1.EmployeeID = 5 
            THEN 'Sale Freight' 
            ELSE '' END SaleType
  FROM Orders T1
 WHERE T1.OrderDate BETWEEN '1996-07-01' AND '1996-12-31'

CASE문에서 WHEN에 조건이 하나가 아닌 둘 이상을 사용할 수 있습니다.

- 결과 -

SQL문 실습 - CASE문(2)

SQL문 실습 - CASE문(3)

이번 글에서는 여기까지 CASE문에 대한 예제를 풀어보겠습니다.

다음 글에서도 이어서 CASE문에 대한 예제를 풀어보면서 CASE문을 알아보겠습니다.

참고 : SQL Server 기본 SQL문 교육