[MSSQL]根据日期返回星座

SQL星座查询函数

最新推荐文章于 2021-02-05 18:22:56 发布

最新推荐文章于 2021-02-05 18:22:56 发布 · 582 阅读

文章标签：

#数据库

本文介绍了一个使用SQL编写的函数，该函数可以根据输入的日期参数返回对应的星座名称。通过复杂的日期比较逻辑，确保了星座判断的准确性。

go --创建函数(优快云 fredrickhu(小F)提供) create function udf_GetStar (@ datetime) RETURNS varchar(100) -- 返回日期所属星座 BEGIN RETURN ( select max(star) from ( select '魔羯座' as star,1 as [month],1 as [day] union all select '水瓶座',1,20 union all select '双鱼座',2,19 union all select '牡羊座',3,21 union all select '金牛座',4,20 union all select '双子座',5,21 union all select '巨蟹座',6,22 union all select '狮子座',7,23 union all select '处女座',8,23 union all select '天秤座',9,23 union all select '天蝎座',10,24 union all select '射手座',11,22 union all select '魔羯座',12,22 ) stars where dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1 = ( select max(dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1) from ( select '魔羯座' as star,1 as [month],1 as [day] union all select '水瓶座',1,20 union all select '双鱼座',2,19 union all select '牡羊座',3,21 union all select '金牛座',4,20 union all select '双子座',5,21 union all select '巨蟹座',6,22 union all select '狮子座',7,23 union all select '处女座',8,23 union all select '天秤座',9,23 union all select '天蝎座',10,24 union all select '射手座',11,22 union all select '魔羯座',12,22 ) stars where @ >= dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1 ) ) end --测试示例 select dbo.udf_GetStar('2010-05-04') select dbo.udf_GetStar('2009-01-04') select dbo.udf_GetStar('2007-12-04') --运行结果 /* 金牛座魔羯座射手座 */