Usage of case when in sql-SQL-php.cn

Usage of case when in sql

angryTom

Release： 2020-02-13 16:35:04

Original

22500 people have browsed it

Usage of case when in sql

Usage of case when in sql

case has two formats. Simple case function and case search function.

1. Simple case function

case sex
  when &#39;1&#39; then &#39;男&#39;
  when &#39;2&#39; then &#39;女’
  else &#39;其他&#39; end

Copy after login

2. Case search function

case when sex = &#39;1&#39; then &#39;男&#39;
     when sex = &#39;2&#39; then &#39;女&#39;
     else &#39;其他&#39; end</span>

Copy after login

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 (&#39;a&#39;,&#39;b&#39;) then &#39;第一类&#39;
     when col_1 in (&#39;a&#39;) then &#39;第二类&#39;
     else &#39;其他&#39; end

Copy after login

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

Copy after login

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 &#39;男&#39;
      when 2 then &#39;女&#39;
      else &#39;空的&#39;
      end
     )性别
  from users u;
                                    ID NAME                        SEX 性别
--------------------------------------- -------------------- ---------- ------
                                      1 张一                            空的
                                      2 张二                          1 男
                                      3 张三                            空的
                                      4 张四                            空的
                                      5 张五                          2 女
                                      6 张六                          1 男
                                      7 张七                          2 女
                                      8 张八                          1 男

Copy after login

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 &#39;男&#39;
      when 2 then &#39;女&#39;
      else &#39;空的&#39;
      end
    )性别
  from users u;
 
                                     ID NAME                 性别
--------------------------------------- -------------------- ------
                                      1 张一                 空的
                                      2 张二                 男
                                      3 张三                 空的
                                      4 张四                 空的
                                      5 张五                 女
                                      6 张六                 男
                                      7 张七                 女
                                      8 张八                 男

Copy after login

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

Copy after login

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!