Case of SQL when and then usage (for categorical statistics)

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

--Simple case function when '  1 ' then ' Male ' when  ' 2 ' Then ' Women '  Else ' other ' end--case search function case when sex = ' 1 ' Then ' Man ' 
   
    when sex = ' 2 ' Then ' women ' else '     other ' end  
   

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 the "second class" case when col_1 in (' A ', ' B ') then ' first Class ' When     col_1 in (' a ') then ' the second class '     Else ' other ' E nd  

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

Take order statistics for example, front-end display histogram (jquery statistics):

The table and the main fields are described below; Table name: Orders
1. Date Createtime
2. Amount Amount
3. User UserID

Situation One:
According to the Department statistics of a year monthly sales (query a department monthly statistics)

1) directly in the SQL statement to determine the monthly information, benefits, direct call from the foreground, the disadvantage, performance is not high.

SQL statements:

SELECT SUM (case if month (s.createtime) = 1 then s.amount ELSE 0 END) as ' January ', SUM (case when month (s.createtime) = 2 Then S.amount else 0 end) as ' February ', sum (case if month (s.createtime) = 3 then S.amount ELSE 0 END) as ' March ', sum (case when month (s) . Createtime) = 4 then S.amount else 0 end) as ' April ', SUM (case when MONTH (s.createtime) = 5 then S.amount else 0 end) as ' May ', SUM (case if month (s.createtime) = 6 then S.amount ELSE 0 END) as ' June ', sum (case when month (s.createtime) = 7 then S.amoun T ELSE 0 end) as ' July ', sum (case if month (s.createtime) = 8 then S.amount ELSE 0 END) as ' August ', sum (case when month (s.create Time) = 9 then S.amount ELSE 0 end) as ' September ', sum (case when MONTH (s.createtime) = Ten then S.amount else 0 END) as ' October ', sum (C ASE when month (s.createtime) = one then S.amount ELSE 0 END) as ' November ', SUM (case when month (s.createtime) = All then S.amount ELSE 0 END) as ' December ' from Orders as Swhere year (s.createtime) = 2014

--Other conditions

Results:

January    February    March April May June July August September October November    December 0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    741327.00    120505.00    0.00

2) Statistics of the value of the month in the database, and then the front-end logic to judge the other months complement 0

SQL statements:

Selectuserid,month (Createtime) as month, SUM (Amount) as Statistics fromorderswhereyear (createtime) = 2014--This assumes you want to check the 2014-year monthly Millions --Other conditions group Byuserid, MONTH (createtime)

Results:
Monthly    Sales    741327.0011    120505.00

Situation Two:
Statistics of monthly sales for all departments in one year

1) This large amount of data impact performance, SQL statements (not found in the Department table):

 SELECT Userid,sum (case is month (s.createtime) = 1 then s.amount ELSE 0 END) as ' January ', SUM (case when month (S.createtim e) = 2 then S.amount else 0 end) as ' February ', sum (case when MONTH (s.createtime) = 3 then S.amount ELSE 0 END) as ' March ', sum (case When month (s.createtime) = 4 then S.amount else 0 END) as ' April ', SUM (case when month (s.createtime) = 5 then S.amount else 0  END) as ' May ', sum (case if month (s.createtime) = 6 then S.amount ELSE 0 END) as ' June ', sum (case when month (s.createtime) = 7 Then S.amount else 0 end) as ' July ', sum (case when MONTH (s.createtime) = 8 then S.amount ELSE 0 END) as ' August ', sum (case when M Onth (s.createtime) = 9 then S.amount else 0 end) as ' September ', SUM (case when MONTH (s.createtime) = Ten then S.amount else 0 end) As ' October ', sum (case when month (s.createtime) = one then S.amount ELSE 0 END) as ' November ', sum (case when month (s.createtime) = T HEN s.amount ELSE 0 END) as ' December ' from Orders as Swhere year (s.createtime) = The GROUP by UserID 

Results:

UserID    January    February March April May June July August September October November    December 1    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    53495.00 0.002    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    738862.00    37968.00    0.003    0.00    0.00    0.00    0.00    0.00    0.00 0.00 0.00    0.00    2099.00    22849.00    0.004    0.00    0.00    0.00    0.00 0.00 0.00    0.00    0.00    0.00    366.00    0.00    0.005    0.00    0.00    0.00    0.00 0.00    0.00    0.00    0.00    0.00    0.00    6193.00    0.00

2) Baidu saw someone mention the list of career change, did not see the instance, not very clear concrete implementation way. Have a friend to know, please inform, thank you!

Selectuserid,month (Createtime) as month, SUM (Amount) as Statistics fromorderswhereyear (createtime) = 2014--This assumes you want to check the 2014-year monthly Millions GROUP Byuserid,month (createtime) results: UserID    Month    statistics 1    738862.002    2099.003    366.004    53495.001    37968.002    22849.00

5 11 6193.00

One last example: Case Classification displays information in relation to different tables based on attribute values　　

That is, you query a different table based on the value of a field. Requirements are: According to Employeeexam's Employeetype (0, on behalf of internal staff, 1 for external employees) to query the corresponding internal employee table or the gender in the external employee table, and according to the Employeeexam Employeetype the name of the department that the employee belongs to in the Department table (the employee table is associated with the Employeeexam table, the employee table is associated with the department table).

Select G.employeename,case g.employeetype when ' 0 ' then (select sex from employee_in WHERE idcode=g.employeeid) ELSE (Selec T sex from Employee_out WHERE idcode=g.employeeid) END Sex,g.employeeid,g.exammethod (case G.employeetype when ' 0 '  Then '  internal employee '  when  ' 1 ' then  ' external employee ' ELSE ' empty ' END ' employeetype,case g.employeetype when ' 0 ' then (SELECT Department.departmentname from Employee_in,department  WHERE Idcode=g.employeeid and department.departmentid= Employee_in.departmentid) ELSE (SELECT unit.name from Unit,employee_out WHERE Idcode=g.employeeid and Employee_ Out.unitid=unit.unitid) END departmentname,case g.employeetype when ' 0 ' then (SELECT employee_in.trainstatus from employee_in where Idcode=g.employeeid) ELSE (SELECT employee_out.trainstatus from Employee_out WHERE idcode= G.employeeid) END Trainsuatusfrom Employeeexam g

　　Resolution : Query Gender: Sex If Employeeexam.employeetype is 0, query the employee_in table for the corresponding employee gender; If Employeeexam.employeetype is 1, query the Employee_out table for the gender of the employee;

Query Employee Type: Employeetype if 0 represents an internal employee, if 1 represents an external employee, the other is empty.

Query Employee Department Name: Departmentname if Employeeexam.employeetype is 0, query departmentname in the Department table (based on employeeexam.idcode= G.employeeid and Department.departmentid=employee_in.departmentid); if Employeeexam.employeetype is 1, query the name in the Unit table.

Query training: trainsuatus similar to sex

Results:

Example: When querying a role, determine if a role has been configured based on the total number of records in the Permissions role table

Sql:

Select  role.*,  (case (SELECT COUNT (Rolepermissionid) from rolepermission WHERE Roleid = Role.roleid) when 0 Then ' ' ELSE ' configured ' END ' is not configured    Haspermissionfrom role

Results:

Case of SQL when and then usage (for categorical statistics)