例1

Paste_Image.png

Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B

|
|
|

解法一：

SELECT name,
CASE WHEN continent ='Europe' or continent ='Asia' THEN 'Eurasia'
     WHEN continent ='North America' or continent ='South America' or continent ='Caribbean' THEN 'America'
ELSE continent 
END
FROM world
WHERE name LIKE 'A%' OR name LIKE 'B%'

解法二：

SELECT name,
CASE WHEN continent in ('Europe','Asia') THEN 'Eurasia'
     WHEN continent in ('North America','South America','Caribbean') THEN 'America'
ELSE continent 
END
FROM world
WHERE name LIKE 'A%' OR name LIKE 'B%'

---

例2

The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.
Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

|
|
|

SELECT winner,subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Chemistry','Physics'),subject,winner

---

例3

把年龄分组并计算每组人数

|
|
|

mysql> SELECT COUNT(Age),(CASE
    -> WHEN Age <=20 THEN '20岁及其以下'
    -> WHEN Age >20 AND Age <=30 THEN '21-30岁'  
    -> WHEN Age >30 AND Age <=40 THEN '31-40岁'
    -> ELSE '40岁以上' END) AS live
    -> FROM user
    -> GROUP BY live;
+------------+--------------+
| COUNT(Age) | live         |
+------------+--------------+
|       8437 | 20岁及其以下 |
|      27037 | 21-30岁      |
|      17093 | 31-40岁      |
|       6534 | 40岁以上     |
+------------+--------------+
4 rows in set

---

例4

提取OrderDate中的年月日

|
|
|

mysql> SELECT *,
    -> EXTRACT(YEAR FROM OrderDate) AS 年,
    -> EXTRACT(MONTH FROM OrderDate) AS 月,
    -> EXTRACT(DAY FROM OrderDate) AS 日
    -> FROM orders
    -> GROUP BY OrderDate
    -> LIMIT 20;
+-----+-----------+---------------------+--------+-----------+-------+----------+------------+------+----+----+
| ID  | OrderID   | OrderDate           | UserID | ProductID | Price | Quantity | TotalMoney | 年   | 月 | 日 |
+-----+-----------+---------------------+--------+-----------+-------+----------+------------+------+----+----+
|   1 | 119000001 | 2011-09-01 00:00:00 | 119970 | A         | 100   |        6 |        600 | 2011 |  9 |  1 |
|  23 | 119000023 | 2011-09-01 00:01:00 | 104629 | A         | 100   |        3 |        300 | 2011 |  9 |  1 |
|  47 | 119000047 | 2011-09-01 00:02:00 | 103868 | B         | 200   |        6 |       1200 | 2011 |  9 |  1 |
|  74 | 119000074 | 2011-09-01 00:03:00 | 110405 | A         | 100   |        8 |        800 | 2011 |  9 |  1 |
|  93 | 119000093 | 2011-09-01 00:04:00 | 113438 | A         | 100   |        8 |        800 | 2011 |  9 |  1 |
| 111 | 119000111 | 2011-09-01 00:05:00 | 113265 | A         | 100   |        4 |        400 | 2011 |  9 |  1 |
| 133 | 119000133 | 2011-09-01 00:06:00 | 105236 | E         | 400   |        9 |       3600 | 2011 |  9 |  1 |
| 155 | 119000155 | 2011-09-01 00:07:00 | 108285 | B         | 200   |        9 |       1800 | 2011 |  9 |  1 |
| 178 | 119000178 | 2011-09-01 00:08:00 | 110595 | D         | 300   |        3 |        900 | 2011 |  9 |  1 |
| 200 | 119000200 | 2011-09-01 00:09:00 | 105668 | A         | 100   |       10 |       1000 | 2011 |  9 |  1 |
| 225 | 119000225 | 2011-09-01 00:10:00 | 100954 | D         | 300   |        8 |       2400 | 2011 |  9 |  1 |
| 248 | 119000248 | 2011-09-01 00:11:00 | 101675 | E         | 400   |        2 |        800 | 2011 |  9 |  1 |
| 272 | 119000272 | 2011-09-01 00:12:00 | 107291 | A         | 100   |       10 |       1000 | 2011 |  9 |  1 |
| 290 | 119000290 | 2011-09-01 00:13:00 | 102565 | D         | 300   |        1 |        300 | 2011 |  9 |  1 |
| 320 | 119000320 | 2011-09-01 00:14:00 | 102565 | D         | 300   |        7 |       2100 | 2011 |  9 |  1 |
| 356 | 119000356 | 2011-09-01 00:15:00 | 115174 | A         | 100   |        1 |        100 | 2011 |  9 |  1 |
| 380 | 119000380 | 2011-09-01 00:16:00 | 104122 | B         | 200   |        7 |       1400 | 2011 |  9 |  1 |
| 401 | 119000401 | 2011-09-01 00:17:00 | 100644 | A         | 100   |       10 |       1000 | 2011 |  9 |  1 |
| 430 | 119000430 | 2011-09-01 00:18:00 | 101176 | A         | 100   |        6 |        600 | 2011 |  9 |  1 |
| 457 | 119000457 | 2011-09-01 00:19:00 | 111251 | D         | 300   |        3 |        900 | 2011 |  9 |  1 |
+-----+-----------+---------------------+--------+-----------+-------+----------+------------+------+----+----+
20 rows in set

---

例5

The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.
Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

|
|
|

SELECT winner,subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Chemistry','Physics'),subject,winner

---

例6

Q:Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

|
|
|

SELECT name, continent, population FROM world x
WHERE 25000000>=ALL (
SELECT population FROM world y
WHERE x.continent=y.continent
)

---

---

---

---