[MSSQL] 조건문 CASE (WHERE, ORDER BY 등)

[MSSQL] 조건문 CASE (WHERE, ORDER BY 등)

WHERE 및 서브쿼리, 정렬(ORDER BY) 등 많은 곳에 응용 가능한 CASE 문 입니다.

예제로 알아봅시다.

SELECT CASE WHEN YEAR(GETDATE()) > 2016 THEN '병신년 아님' WHEN YEAR(GETDATE()) = 2016 THEN '병신년' ELSE '몰라' END AS '띠' DECLARE @TABLE TABLE( num INT identity(1,1), a INT, b INT, c INT ) INSERT INTO @TABLE VALUES(1,2,3) INSERT INTO @TABLE VALUES(2,2,3) INSERT INTO @TABLE VALUES(3,4,3) -- WHERE CASE 1 SELECT  *  FROM @TABLE  WHERE ( CASE  WHEN a > c THEN a WHEN a = c THEN  b WHEN a < c THEN c ELSE 0 END ) > b -- WHERE CASE 1 SELECT  *  FROM @TABLE  WHERE b = ( CASE  WHEN a > c THEN a WHEN a = c THEN b WHEN a < c THEN c ELSE 0 END ) SELECT  *  FROM @TABLE  ORDER BY ( CASE WHEN YEAR(GETDATE()) = '2016' THEN a WHEN YEAR(GETDATE()) = '2017' THEN b WHEN YEAR(GETDATE()) = '2018' THEN c ELSE num END ) ASC