Tips for using case when then in SQL collection functions

So what is the use of the Set function?

Suppose the database has a table named student.

If you want to use this table to find out the numbers of men and women in Jiangxi province, the number of boys in Guangdong province, and the number of men and women in Zhejiang province, how do you write SQL statements? Generate the following result table

The answer is: select sex, count (case province when 'guangdong 'then' Guangdong 'end) as Guangdong province, count (case province when 'jiangxi 'then' Jiangxi 'end) as Jiangxi province, count (case province when 'then' Zhejiang 'end') as Zhejiang province from student group by sex

The count () function counts the number of rows based on the given range and group by (statistical method ).

We will understand the above statements step by step

1. select sex from student (query the number of men and women in the data table)

2. select sex, count (*) as num from student group by sex (query the number of men and women in the table)

3. select sex, province, count (*) as num from student group by sex, province (query the number of men and women in each province)

The point is, what if I replace * in count (*) with any column name? What will count (province) do?

4. select sex, province, count (province) as num from student group by sex, province (query the number of men and women in each province)

The result is the same. In addition, count (province) is equivalent to count (case province when 'then' of Zhejiang province 'else province end of Zhejiang province)

But if we narrow down the scope, that is, count (case province when 'then' Zhejiang 'end'), please refer to the following

5. select sex, province, count (case province when 'then' Zhejiang 'end') as num from student group by sex, province

That is to say, the scope of statistics on the quantity of men and women is limited in Zhejiang Province.

6. select sex, count (case province when 'then' Zhejiang 'end') as Zhejiang province from student group by sex

It's close to our requirements. Now we only need to add a few other fields.

7. select sex, count (case province when 'guangdong 'then' Guangdong 'end) as Guangdong province, count (case province when 'jiangxi 'then' Jiangxi 'end) as Jiangxi province, count (case province when 'then' Zhejiang 'end') as Zhejiang province from student group by sex

Summary: Of course, there are many ways to concatenate multiple subqueries. This is just a way of thinking.

Supplement: case when then knowledge points

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

If the default range is not full, the Guangdong Province of the empty image is empty.

(2) select (case province when 'then' Zhejiang province 'when' Jiangxi province 'then' Jiangxi province 'else province end) as province from student