Usage of case when in sql
case has two formats. Simple case function and case search function.
1. Simple case function
case sex when '1' then '男' when '2' then '女’ else '其他' end
2. Case search function
case when sex = '1' then '男' when sex = '2' then '女' else '其他' end</span>
These two methods can achieve the same function. The simple case function is relatively simple to write, but compared with the case search function, there are some limitations in functionality, such as writing predicates. (Free learning video tutorial recommendation: mysql video tutorial)
There is another issue that needs attention. The case function only returns the first qualified value, and the remaining case part will are automatically ignored.
For example, in the following sql, you will never get the result of "second type"
case when col_1 in ('a','b') then '第一类' when col_1 in ('a') then '第二类' else '其他' end
Example demonstration:
First create a users table, which contains There are three fields: id, name, and sex. The table content is as follows:
select * from users ID NAME SEX ---------- -------------------- ---------- 1 张一 2 张二 1 3 张三 4 张四 5 张五 2 6 张六 1 7 张七 2 8 张八 1
1. The "sex" in the results of the above table is expressed in code. I hope the code can be expressed in Chinese. You can use the case statement in the statement:
select u.id,u.name,u.sex, (case u.sex when 1 then '男' when 2 then '女' else '空的' end )性别 from users u; ID NAME SEX 性别 --------------------------------------- -------------------- ---------- ------ 1 张一 空的 2 张二 1 男 3 张三 空的 4 张四 空的 5 张五 2 女 6 张六 1 男 7 张七 2 女 8 张八 1 男
2. If you do not want the "sex" column to appear in the list, the statement is as follows:
select u.id,u.name, (case u.sex when 1 then '男' when 2 then '女' else '空的' end )性别 from users u; ID NAME 性别 --------------------------------------- -------------------- ------ 1 张一 空的 2 张二 男 3 张三 空的 4 张四 空的 5 张五 女 6 张六 男 7 张七 女 8 张八 男
3. Combine sum with case to achieve splitting segment statistics.
If you now want to count the number of people of various genders in the above table, the sql statement is as follows:
select sum(case u.sex when 1 then 1 else 0 end)男性, sum(case u.sex when 2 then 1 else 0 end)女性, sum(case when u.sex <>1 and u.sex<>2 then 1 else 0 end)性别为空 from users u; 男性 女性 性别为空 ---------- ---------- ---------- 3 2 0 -------------------------------------------------------------------------------- SQL> select count(case when u.sex=1 then 1 end)男性, count(case when u.sex=2 then 1 end)女, count(case when u.sex <>1 and u.sex<>2 then 1 end)性别为空 from users u; 男性 女 性别为空 ---------- ---------- ---------- 3 2 0
The above is the detailed content of Usage of case when in sql. For more information, please follow other related articles on the PHP Chinese website!