use northwindgoCREATE FUNCTION GetStar(@ datetime)RETURNS varchar(100)ASBEGIN--僅一句 SQL 搞定RETURN(--declare @ datetime--set @ = getdate()select max(star)from(-- 星座,該星座開始日期所屬月,該星座開始日期所屬日select '魔羯座' as star,1 as [month],1 as [day]union all select '水瓶座',1,20union all select '雙魚座',2,19union all select '牧羊座',3,21union all select '金牛座',4,20union all select '雙子座',5,21union all select '巨蟹座',6,22union all select '獅子座',7,23union all select '處女座',8,23union all select '天秤座',9,23union all select '天蝎座',10,24union all select '射手座',11,22union all select '魔羯座',12,22) starswhere 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,20union all select '雙魚座',2,19union all select '牧羊座',3,21union all select '金牛座',4,20union all select '雙子座',5,21union all select '巨蟹座',6,22union all select '獅子座',7,23union all select '處女座',8,23union all select '天秤座',9,23union all select '天蝎座',10,24union all select '射手座',11,22union all select '魔羯座',12,22) starswhere @ >= dateadd(day,[day]-1,dateadd(month,[month]-1,dateadd(year,datediff(year,0,@),0)))))endgo--測試use northwindselect dbo.getstar(birthdate),count(*)from employeesgroup by dbo.getstar(birthdate)