Hello, everyone. I'm Mr. Tony, who only talks about technology and doesn't cut his hair.

Today, we introduce an application CASE of CASE conditional expression, using the example table Click here to download.

If the company is about to celebrate its 20th anniversary, it plans to distribute an anniversary gift to all employees. The rules for gift distribution are as follows:

• As of 2020, for employees with less than 10 years of employment, the gift of male employees is a watch and that of female employees is a set of cosmetics;
• For employees who have been employed for 10 years but less than 15 years by 2020, the gift for male employees is a mobile phone and for female employees is a necklace;
• For employees who have been employed for 15 years by 2020, gifts for both men and women are unified into one computer.

Now the HR department needs to know what gifts are given to each employee and how to get this information through query statements?

The search CASE expression is very suitable for the processing of such logical conditions. We can use the following statements:

-- Oracle,MySQL as well as PostgreSQL
SELECT emp_name AS "Employee name", hire_date AS "Entry date",
     CASE
       WHEN EXTRACT(YEAR FROM hire_date)> 2011 AND sex = 'male' THEN 'Wrist watch'
       WHEN EXTRACT(YEAR FROM hire_date) > 2011 AND sex = 'female' THEN 'Cosmetics'
       WHEN EXTRACT(YEAR FROM hire_date) > 2006 AND sex = 'male' THEN 'mobile phone'
       WHEN EXTRACT(YEAR FROM hire_date) > 2006 AND sex = 'female' THEN 'Necklace'
       ELSE 'computer'
     END AS "gift"
FROM employee;

WHEN executing the above statement, first judge whether the condition in the first WHEN clause (male employees with less than 10 years of employment) is true. If it is true, return the result (watch) in the corresponding THEN clause; If not, continue to judge whether the condition in the second WHEN clause (female employees with less than 10 years of employment) is true. If it is true, return the result (cosmetics) in the corresponding THEN clause; And so on, if no condition is true, the default result (computer) in the ELSE clause is returned.

In addition to searching the CASE expression, we also use the EXTRACT function to EXTRACT the employee's employment year. Therefore, the above query is applicable to Oracle, MySQL and PostgreSQL. The results returned by the query are as follows.

Employee name|Entry date   |gift
------|----------|---
Liu Bei   |2000-01-01|computer
 Guan Yu   |2000-01-01|computer
 Fei Zhang   |2000-01-01|computer
...
Liao Hua   |2009-02-17|mobile phone
 guan ping   |2011-07-24|mobile phone
 Zhao Shi   |2011-11-10|Necklace
...

Microsoft SQL Server can use DATAPART function to extract information from date, for example:

-- Microsoft SQL Server
SELECT emp_name AS "Employee name", hire_date AS "Entry date",
     CASE
       WHEN DATEPART(YEAR, hire_date) > 2011 AND sex = 'male' THEN 'Wrist watch'
       WHEN DATEPART(YEAR, hire_date) > 2011 AND sex = 'female' THEN 'Cosmetics'
       WHEN DATEPART(YEAR, hire_date) > 2006 AND sex = 'male' THEN 'mobile phone'
       WHEN DATEPART(YEAR, hire_date) > 2006 AND sex = 'female' THEN 'Necklace'
       ELSE 'computer'
     END AS "gift"
FROM employee;

SQLite can use the STRFTIME function to extract information from the date, for example:

-- SQLite
SELECT emp_name AS "Employee name", hire_date AS "Entry date",
     CASE
       WHEN CAST(STRFTIME('%Y', hire_date) AS INT) > 2011 AND sex = 'male'
       THEN 'Wrist watch'
       WHEN CAST(STRFTIME('%Y', hire_date) AS INT) > 2011 AND sex = 'female'
       THEN 'Cosmetics'
       WHEN CAST(STRFTIME('%Y', hire_date) AS INT) > 2006 AND sex = 'male'
       THEN 'mobile phone'
       WHEN CAST(STRFTIME('%Y', hire_date) AS INT) > 2006 AND sex = 'female'
       THEN 'Necklace'
       ELSE 'computer'
     END AS "gift"
FROM employee;

The data type returned by the STRFTIME function is a string, which we convert to an integer through the CAST function.

The above case comes from the book SQL programming ideas.