Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use case when in sql, which has certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let Xiaobian take you to understand it together.

Use of SQL case when

Case has two formats. Simple case functions and case search functions.

1. Simple case function

case sex when '1' then 'male' when '2' then 'female' else ' end

2. Case search function

case when sex = '1' then 'male' when sex = '2' then 'female' else ' end

Both of these methods can achieve the same function. Simple case functions are relatively simple to write, but compared to case search functions, there are some limitations in terms of functionality, such as writing discriminative expressions. (Free learning video tutorial recommended: mysql video tutorial)

There is also a caveat: the case function returns only the first value that matches the condition, and the rest of the case is automatically ignored.

For example, in the following sql, you can never get the result "second class"

case when col_1 in ('a ',' b') then 'first class' when col_1 in ('a ') then ' second class ' else ' end

Example demonstration:

First create a users table, which contains id,name,sex three fields, the table content is as follows:

select * from users ID NAME SEX---------- -------------------- ----------1 Zhang Yi 2 zhanger 13 Zhang San 4 zhangsi 5 fives 26 zhangliu 17 Sevens 28 zhangba 1

1. The "sex" in the results in the above table is represented by code, and it is hoped that the code will be represented in Chinese. You can use case statements in statements:

select u.id,u.name,u.sex, (case u.sex when 1 then 'male' when 2 then 'female' else 'empty' end gender from users u; ID NAME SEX Sex--------------------------------------------------------------------------- 1 piece of one empty Two twos. 1 man Three threes. empty Four fours. empty Five fives. 2 women Six sixes. 1 man Seven sevens. 2 women Eight eights. 1 man

If you do not want the "sex" column in the list, the statement is as follows:

select u.id,u.name, (case u.sex when 1 then 'male' when 2 then 'female' else 'empty' end gender from users u; ID NAME Gender----------------------------------------------------------------- 1 piece of one empty Two twos. male Three threes. empty Four fours. empty Five fives. female Six sixes. male Seven sevens. female Eight eights. male

3. Combining sum with case can realize segmented statistics.

If you now want to count the number of people of each gender in the table above, the sql statement is as follows:

select sum(case u.sex when 1 then 1 else 0 end) Male, sum(case u.sex when 2 then 1 else 0 end) Female, sum(case when u.sex 1 and u.sex2 then 1 else 0 end) Gender is null from users u; male women Gender is blank------------------------------ 3 2 0 --------------------------------------------------------------------------------SQL> select count(case when u.sex =1 then 1 end) Male, count(case when u.sex =2 then 1 end) Female, count(case when u.sex 1 and u.sex2 then 1 end) Gender is null from users u; male female Gender is blank------------------------------ 3 2 0 Thank you for reading this article carefully. I hope that the article "How to use case when in sql" shared by Xiaobian will be helpful to everyone. At the same time, I hope that everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.