Introduction to SQL Server---case-when usage

Introduction to Casewhen Usage

The previous blog used Casewhen to solve the problem of data aggregation, then this blog I will briefly describe the use of case.

The expression form of casewhen
1. Simple Case function

2. Case Search function

--case search function Casewhen lettertype= ' cadre Letter of recommendation ' then ' 1 ' when lettertype= ' transmitting file Notice ' then ' 2 ' else ' other ' END

The simple case function is relatively concise, but there are some limitations in function, such as write-judgement, compared to the search function. There is also a problem to be aware that the casefunction returns only the first qualifying value, and the remaining case section is automatically ignored.

Casewhen the use of different positions in a statement

1. SELECT Case When usage

Select UserID, COUNT (case when lettertype= ' cadre letter of introduction ' Then ' 1 ' end) cadre of letter of introduction number, COUNT (case when lettertype= ' transmitting file Notice ' then ' 1 ' end) transmitting File notification singular from T_lettersrecord GROUP by UserID

Operation Result:

2. WHERE Case when usage

SELECT L.lettertype, u.realname from   T_lettersrecord as L, t_user as U  WHERE (case when l.lettertype = ' Cadre Letter of introduction ' and                   U.userid = ' 1 '                THEN1              whenl.lettertype = ' Cadre Letter of introduction ' and                   u.userid <> ' 1 '                THEN1              ELSE0           END) = 1

Operation Result:

3. Use of GROUP by case

SELECT Case when salary <= "T1" when salary > and salary <=8000 then  ' T2 ' when salary > 8000 and salary <=12000 then  ' T3 ' when salary > 12000 and Salary <= 20000 then ' T4 ' ELSE NULL END level name,--alias name Cou NT (*) from T_usersalarygroup by case when    salary <= "T1" when salary > and salary <=8000  t HEN ' T2 ' when salary > 8000 and salary <=12000 then  ' T3 ' when salary > 12000 and Salary <= 20000 then ' T4 ' E LSE NULL END;

Operation Result:

This article briefly introduces the simple use of casewhen, hoping to be helpful to the reader.

Reference article:

Http://www.cnblogs.com/eshizhan/archive/2012/04/06/2435493.html (Eshizhan)

Http://www.cnblogs.com/yazdao/archive/2009/12/09/1620482.html ( Shadow Network Technology Co., Ltd. )

Introduction to SQL Server---case-when usage