Case of SQL Learning THEN Else End Usage _ database Other

Super: Case...when...then...else...end conditional query in SQL command (different from where) and type conversion usage

Case...when...then...else...end, which is in front of the from, can change the value of a field in a record and cannot decide whether or not to display the record;

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

Case...when...then...else...end, which can be used to sum multiple fields of the same record, with branch judgments.

In addition, for field judgment and processing, it is often necessary to force type conversions.

Select To_number (' 19f ', ' xxx ') from dual; --Eight system
Get 415
Select To_number (' F ', ' xx ') from dual; --16 system
Get 15

Select To_number (' 123 ') from dual; --Decimal
Get 123

The biggest relationship with date operations is two conversion functions: To_date (), To_char ()
The to_date () action converts a character type to a date type in a certain format:
Specific usage: to_date (' 2004-11-27 ', ' yyyy-mm-dd '),
The former is a string, the latter is a date format, note that the two should be a corresponding. such as To_date (' 2004-11-27 13:34:43 ', ' yyyy-mm-dd hh24:mi:ss ') will get specific time

String handler function:

Select To_number (SUBSTR (rain_1,0,2), ' xx ') from Obs

(i) Access database

You know there is an IIf function in Access that simplifies a judgment assignment sequence into an expression, such as
IIF (a>b,1,2), if it does a>b then the result gives 1, otherwise it is 2. It's really convenient.

Example
(1) Digital
If Measures.CurrentMember is an empty cell, the following example returns 0, otherwise it returns 1
IIf (IsEmpty (Measures.CurrentMember), 0, 1)

(2) string
If Measures.CurrentMember is an empty cell, 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 totals, for example, to know the number of users who should actually pay the fee
Access: Select sum (IIF (Amount >0, 1,0)) as Num from cost

MS SQL notation: Select sum (case when amount >0 then 1 else 0 end) as Num from

(ii) Ms SQL database

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

Example: Select Id,case when bz= ' 1 ' then xx. bz= ' 2 ' then yy else ZZ end as TT from XXX

Here, 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 results:

ID Bzh Zynr Fbrq
001 <p> Hello </p> 2010-05-07 0:00:00
NULL

I don't want the results above, I want the following result:

ID Bzh Zynr Fbrq
001 <p> Hello </p> 2010-05-07 0:00:00
15 No 1990-06-06 0:00:00

That is, when the standard number is empty, I want it to display null, and when the release date is null I want it to display the date I specified

Here I write, you can think for yourself

Select Id,zynr (case when
bzh= ' then ' no ' else Bzh end) as Bzh, (case when
FBRQ is null then ' 1982-06-02 ' else fb RQ end) as Fbrq from 
policy_fgxx

SQLite case if then else end

Sqlite> Select DateTime (createtime, ' Unixepoch ', ' localtime ') time, message content, case Des time 0 Then ' le ' Else ' garden ' end name 
   from chat_af863d30e1c1e5eba27a0df37a75dba0 where rowID <= 2;
+---------------------+----------+------+
| time        | content |   name |
+---------------------+----------+------+
| 2013-08-22 18:54:13 | Bad network | |  |
2013-08-22 20:19:07 | Cholle   | 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 ' DECP1 ' THEN 1 ELSE 0) as DECP1,
SUM (case DECP when ' decp2 ' then 1 else 0) as Decp2
From teacher

Generated:

DEPT1 Dept2

2 1

This example is primarily a study of the use of the (case THEN ELSE) statement.

This is a very useful statement when we do data statistics.