SQL语句匹配生日对应星座

发表于 | 分类于 |

某张会员表，需要对会员进行按星座的数据分析，因此需要加上星座字段，字段根据生日自动匹配。假设有一张member会员表，表中有一个birthday生日字段（该字段事先以datekey方式存储），那么使用case when语句匹配星座语句如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT constellation = CASE 
  WHEN SUBSTRING(birthday,5,4) BETWEEN 1222 AND 1231 OR SUBSTRING(birthday,5,4) BETWEEN 101 AND 119 THEN '魔羯座'  
  WHEN SUBSTRING(birthday,5,4) BETWEEN 120 AND 218 THEN '水瓶座'
  WHEN SUBSTRING(birthday,5,4) BETWEEN 219 AND 320 THEN '双鱼座'
  WHEN SUBSTRING(birthday,5,4) BETWEEN 321 AND 420 THEN '牡羊座'
  WHEN SUBSTRING(birthday,5,4) BETWEEN 421 AND 520 THEN '金牛座'
  WHEN SUBSTRING(birthday,5,4) BETWEEN 521 AND 621 THEN '双子座'
  WHEN SUBSTRING(birthday,5,4) BETWEEN 622 AND 722 THEN '巨蟹座'
  WHEN SUBSTRING(birthday,5,4) BETWEEN 723 AND 822 THEN '狮子座'
  WHEN SUBSTRING(birthday,5,4) BETWEEN 823 AND 922 THEN '处女座'  
  WHEN SUBSTRING(birthday,5,4) BETWEEN 923 AND 1022 THEN '天秤座'
  WHEN SUBSTRING(birthday,5,4) BETWEEN 1023 AND 1121 THEN '天蝎座'  
  WHEN SUBSTRING(birthday,5,4) BETWEEN 1122 AND 1221 THEN '射手座' 
END 
,birthday
FROM member;

得出结果，之后就可以根据星座对会员做进一步分析

匹配生日对应星座查询结果

坚持原创技术分享，您的支持将鼓励我继续创作！