SQL learning-use CASE WHEN THEN ELSE END

Super powerful: Use case... when... then... else... end condition query (different from where) in SQL commands and type conversion

Case... when... then... else... end is before from. You can change the value of a field in the record and cannot decide whether to display the record;

Where is behind the from clause. You cannot change the value of a field in the record, but you can decide whether to display the record.

Case... when... then... else... end can be used to sum up multiple fields of the same record, with Branch judgment.

In addition, field judgment and processing often require forced type conversion.

Select to_number ('19f', 'xxx') from dual; -- octal
Get 415
Select to_number ('F', 'xx') from dual; -- hexadecimal
Get 15

Select to_number ('123') from dual; -- decimal
Get 123

The biggest relationship with the date operation is the two conversion functions: to_date (), to_char ()
To_date () converts the character type to the date type in a certain format:
Usage: to_date ('2017-11-27 ', 'yyyy-mm-dd '),
The former is a string, and the latter is the date format. Note that the two must be a pair of values. For example, to_date ('2017-11-27 13:34:43 ', 'yyyy-mm-dd hh24: mi: ss') will get the specific time

String processing functions:

Select to_number (SUBSTR (rain_1, 0, 2), 'xx') from obs

(1) Access Database

We all know that there is an iif function in access, which can simplify a sequence of judgment values into an expression, such
Iif (a> B, 1, 2). If a> B is true, 1 is returned; otherwise, 2 is returned. This is very convenient.

Example
(1) Number
If Measures. CurrentMember is an empty unit, the following example returns 0; otherwise, 1 is returned.
IIf (IsEmpty (Measures. CurrentMember), 0, 1)

(2) String
If Measures. CurrentMember is an empty unit, the following string returns the string "Yes"; otherwise, the string "No" is returned"
IIf (IsEmpty (Measures. CurrentMember), "Yes", "No ")

In Access, I can use the IIF function for statistical summary. For example, you need to know the number of users that should be paid.
Access statement: Select sum (iif (amount> 0, 1, 0) as num from fee

Ms SQL statement: select sum (case when amount> 0 then 1 else 0 end) as num from fee

Ms SQL database

IIF in SQL is case when... then... else... end

Example: select id, case when bz = '1' then xx when bz = '2' then yy else zz end as tt from xxx

For example, there is a table of policies and regulations (policy_fgxx) with ID (Primary Key), bzh (standard number), zynr (main content ),

Fbrq (Release Date) four fields

Select * From policy_fgxx result:

ID bzh zynr fbrq
13 001 <p> Hello </p> 2010-05-07 0:00:00
15 NULL

I don't want the above results. I want the following results:

ID bzh zynr fbrq
13 001 <p> Hello </p> 2010-05-07 0:00:00
15 No. 0:00:00

That is to say, when the standard number is null, I want it to be blank. When the release date is null, I want it to display the date I specified.

I will write the statement below. You can think for yourself.

Select id, zynr, (case when bzh = ''then' without 'else bzh end) AS bzh, (case when fbrq is null then '2017-06-02 'else fbrq end) AS fbrq From policy_fgxx

Sqlite case when then else end

Sqlite> select datetime (CreateTime, 'unixepoch ', 'localtime') time, Message content, case Des when 0 then 'else' garden 'end name from Chat_af863d30e1c1e5eba27a0df37a75dba0 where rowid <= 2; + duration + ---------- + ------ + | time | content | Name | + ----------------------- + ---------- + ------ + | 18:54:13 | poor network | garden | 20:19:07 | Joy | garden | + --------------------- + ---------- + ------ + 2 rows in set

Examples of other netizens:

Id name dept
1 aa dept1
2 bb dept1
3 cc dept2

Select sum (CASE decp WHEN 'depp1' THEN 1 ELSE 0 end) AS decp1,
Sum (case decp when 'depp2' then 1 else 0 end) as decp2
FROM teacher

Generation:

Dept1 dept2

2 1

This example mainly describes the usage of the (case when then else end) statement.

This is a very useful statement for data statistics.