Case of SQL when and then usage

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

Press CTRL + C to copy the code

In both of these ways, the same functionality can be achieved. The simple case function is relatively concise, but there are some limitations to the function, such as writing a decision, compared to the search function.

There is also a problem to focus on, the case function returns only the first qualifying value, and the remaining case section is automatically ignored.

--for example, the following SQL, you can never get the result of a "second class" When the case is col_1 in (' A ', ' B ') then ' first Class ' When     col_1 in (' a ') Then ' second '     Else ' other ' end  

The following example shows:
First, create a users table that contains the Id,name,sex three fields, with the following table contents:

Sql> drop table users purge; drop table users purge ORA-00942: Table or view does not exist sql> CREATE table users (ID int,name varchar2 (), sex number); Table createdsql> INSERT into users (Id,name) VALUES (1, ' Zhang Yi '); 1 row insertedsql> insert into users (Id,name,sex) VALUES (2, ' Zhang Yi ', 1); 1 row insertedsql> insert into users (Id,name) VALUES (3, ' Zhang San '); 1 row insertedsql> insert into users (Id,name) VALUES (4, ' Zhang Si '); 1 row insertedsql> insert into users (Id,name,sex) VALUES (5, ' five ', 2); 1 row insertedsql> insert into users (Id,name,sex) VALUES (6, ' Six ', 1); 1 row insertedsql> insert into users (Id,name,sex) VALUES (7, ' seven ', 2); 1 row insertedsql> insert into users (Id,name,sex) VALUES (8, ' eight ', 1); 1 row insertedsql> commit;                                      Commit completesql> SELECT * from users;                                      ID NAME SEX---------------------------------------------------------------------                                                       1 Sheets A2 Sheets 21 3 sheets of three                                      4 45 Sheets 52                                      6 piece 61 7 piece 72 8 Sheets 81 8 rows selected

1, the above table results in the "sex" is expressed in code, I hope that the code in Chinese. You can use a case statement in a statement:

Sql> Select U.id,u.name,u.sex,  2    (case u.sex  3 if      1 Then ' Male '  4 when 2 then      ' Female '  5      Else ' empty '  6      end  7     ) Gender 8 from  users u;                                      ID NAME                        sex sex---------------------------------------------------------------------------                                      1 One                            empty                                      2 two                          1 men                                      3 Three                            empty 4 blank                                      5 sheets five 2                          women                                      6 Sheets six                          1 men                                      7 sheets seven                          2 girls                                      8 sheets Eight                          1 men 8 rows selected

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

Sql> Select U.id,u.name,  2    (case u.sex  3 if      1 Then ' Male '  4 when 2 then      ' Female '  5      Else ' empty '  6      end  7 '     sex  8 from  users u;                                      ID NAME                 sex-----------------------------------------------------------------                                      1                 free                                      2-piece Two                 male                                      3 three                 empty                                      4 four                 empty                                      5 five                 female                                      6 Zhang six                 male                                      7 Zhang Seven                 girls                                      8 Eight                 men 8 rows Selected

3. Combining sum with case can be used to achieve segmented statistics.
If you now want to count the number of genders in the table above, the SQL statement is as follows:

Sql> Select  2    sum (case u.sex if 1 then 1 else 0 end) male,  3    sum (case u.sex while 2 then 1 else 0 end) female,  4    sum (case time U.sex <>1 and u.sex<>2 then 1 else 0 end) gender is null  5 from  users u;         Male         female       sex is empty------------------------------         3          2          0---------------------------------------- ----------------------------------------sql> Select  2    count (case if U.sex=1 then 1 end) male,  3    count (case time u.sex=2 then 1 end) female,  4    count (case time U.sex <>1 and u.sex<>2 then 1 end) Sex is empty  5 from  users u;         Male          female       sex is empty------------------------------         3          2          0

Case of SQL when and then usage