求日期所属星座的 T-SQL UDF (用户自定义函数)

use northwind
go
CREATE FUNCTION GetStar(@ datetime)
RETURNS varchar(100)
AS
BEGIN
--仅一句 SQL 搞定
RETURN
(
--declare @ datetime
--set @ = getdate()
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(day,[day]-1,dateadd(month,[month]-1,dateadd(year,datediff(year,0,@),0)))
=
(
select max(dateadd(day,[day]-1,dateadd(month,[month]-1,dateadd(year,datediff(year,0,@),0))))
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(day,[day]-1,dateadd(month,[month]-1,dateadd(year,datediff(year,0,@),0)))
)
)
end

go
--测试
use northwind
select dbo.getstar(birthdate),count(*)
from employees
group by dbo.getstar(birthdate)

原文转自：http://www.ltesting.net