Syntax and usage of case function

Case function is a kind of aggregate function, which is a statistical function.

Case expression:

CASE selector WHEN value1 THEN action1; WHEN value2 THEN action2; WHEN value3 THEN action3

There are two forms of CASE WHEN expressions

(1) simple Case function

CASE sex WHEN'1' THEN 'male' WHEN'2' THEN 'female' ELSE 'other' END

(2) Case search function

CASE WHEN sex ='1' THEN 'male' WHEN sex ='2' THEN 'female' ELSE 'other' END

This article does not cover the use of case search functions.

II. CASE WHEN application

Student table of student gender statistics:

-query all genders in the table

Select ID, (case sex when'1' then 'male' when'2' then 'female' ELSE 'null' END) AS' gender', province as' province 'from `student`ORDER BY `ID` ASC

The query results are as follows:

Find out the number of boys and girls in Jiangsu Province and Zhejiang Province respectively.

The first method of calculation: distinguishing by province

Select province AS 'Jiangsu', COUNT (case sex when 'male' THEN 'male' ELSE null END) AS 'male', COUNT (case sex when 'female' THEN 'female' ELSE null END) AS 'female' from student where `province` = 'Jiangsu' UNIONselect province AS 'Jiangsu' COUNT (case sex when 'THEN' male 'ELSE null END) AS' boy', COUNT (case sex when 'female' THEN 'female' ELSE null END) AS 'female' from `student` where `province` = 'Zhejiang'

The query results are as follows:

The second calculation: distinguish by sex

Select sex, COUNT (case province when 'THEN' Jiangsu 'ELSE null END) AS' Jiangsu', COUNT (case province when 'Zhejiang' THEN 'Zhejiang' ELSE null END) AS 'Zhejiang' from `student`group by `sex`

The query results are as follows:

Note:

COUNT (case province when 'Jiangsu' THEN 'Jiangsu' ELSE null END) AS 'Jiangsu'

It can be simplified as:

COUNT (case province when 'Jiangsu' THEN 'Jiangsu' END) AS 'Jiangsu'

-inquire about the number of men and women in each province

Select province, sex, COUNT (*) AS 'Total' from student GROUP BY province, sex

The query results are as follows:

-only statistics on the number of men and women in Zhejiang Province

Select sex, province, count (case province when 'Jiangsu' then 'Jiangsu' end) as' quantity 'from student group by province, sex

The query results are as follows:

When there is a lot of data, using the above data query takes up a lot of resources, so it can be simply written in the following form:

Select sex as' gender', count (case province when 'Jiangsu' then 'Jiangsu' end) as' Zhejiang 'from student group by sex

The query results are as follows:

Add:

(1) select (case province when 'Zhejiang' then 'Zhejiang' when 'Jiangsu' then 'Jiangsu' else province end) as province from student

Else province defaults to display other cities except Zhejiang and Jiangsu.

(2) case when can be judged as unequal.

Can be used for =, > =