文章
/
learn / sql
/ 查寻
返回文章列表

这篇文章已经发布超过一年了，内容可能已经过时，请谨慎参考。

查寻

#SQL

CASE 查询示例（生肖）

完整写法

SELECT *,
  CASE
    WHEN YEAR(PeopleBirth) % 12 = 4 THEN '鼠'
    WHEN YEAR(PeopleBirth) % 12 = 5 THEN '牛'
    WHEN YEAR(PeopleBirth) % 12 = 6 THEN '虎'
    WHEN YEAR(PeopleBirth) % 12 = 7 THEN '兔'
    WHEN YEAR(PeopleBirth) % 12 = 8 THEN '龙'
    WHEN YEAR(PeopleBirth) % 12 = 9 THEN '蛇'
    WHEN YEAR(PeopleBirth) % 12 = 10 THEN '马'
    WHEN YEAR(PeopleBirth) % 12 = 11 THEN '羊'
    WHEN YEAR(PeopleBirth) % 12 = 0 THEN '猴'
    WHEN YEAR(PeopleBirth) % 12 = 1 THEN '鸡'
    WHEN YEAR(PeopleBirth) % 12 = 2 THEN '狗'
    WHEN YEAR(PeopleBirth) % 12 = 3 THEN '猪'
  END AS zodiac
FROM people;

简化写法

SELECT *,
  CASE YEAR(PeopleBirth) % 12
    WHEN 4 THEN '鼠'
    WHEN 5 THEN '牛'
    WHEN 6 THEN '虎'
    WHEN 7 THEN '兔'
    WHEN 8 THEN '龙'
    WHEN 9 THEN '蛇'
    WHEN 10 THEN '马'
    WHEN 11 THEN '羊'
    WHEN 0 THEN '猴'
    WHEN 1 THEN '鸡'
    WHEN 2 THEN '狗'
    WHEN 3 THEN '猪'
  END AS zodiac
FROM people;