Case when then else end usage in SQL

Time:2019-11-8

Case end

Similar to if else judgment in Python, case is a field. When the value of this field is’ 1 ‘, it returns’ male’ and ‘2’ returns’ female ‘. End as a title sex

SELECT
    s.s_id,
    s.s_name,
    s.s_sex,
CASE s.s_sex
When '1' then 'male'
When '2' then 'female'
Else 'other'
END as sex,
    s.s_age,
    s.class_id
FROM
    student s

It can also be written in this way. No field is added after case. Specify the field name when!

SELECT
    s.s_id,
    s.s_name,
    s.s_sex,
CASE
When s.s_sex ='1 'then' male '
When s.s_sex ='2 'then' female '
Else 'other'
END as sex,
 s.s_age,
 s.class_id
FROM
    t_b_student s

Case when then else end usage in SQL

An example of grouping

Statistics of the number of men and women in each country

Case when then else end usage in SQL

SELECT
    country,
    Sum (case when p.sex = 1 then p.population else 0 end) as' male ',
    Sum (case when p.sex = 2 then p.population else 0 end) as' female '
FROM people as p 
GROUP BY country

Case when then else end usage in SQL

The total number of people belonging to each continent in the national population table

Case when then else end usage in SQL
Create table and insert data

CREATE TABLE `people` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(55) DEFAULT NULL,
  `population` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)Engine = InnoDB default charset = utf8 comment = Test ';

insert into people (country, population) 
VALUES
('China', 210),
('Japan', 10),
("India", 312312312),
('US', 23);

query

SELECT 
    case p.country 
    When 'China' then 'Asia'
    When 'Japan' then 'Asia'
    When 'India' then 'Asia'
    Else 'North America' 
    End as' continent ',
    Sum (p.population) as' population '
FROM people as p
GROUP BY case p.country
            When 'China' then 'Asia'
            When 'Japan' then 'Asia'
            When 'India' then 'Asia'
            Else 'North America' 
            end

Result
Case when then else end usage in SQL