|
生日问题是个看似简单逻辑上却又比较复杂的小问题
主要的逻辑难点第一个是关于闰月尾天的计算，第二个是判断本年度生日是否已过
本文给出了三种解决办法，
第一种是最常用的解决办法，即常用的SQL语法，不过看起来比较复杂，适合环境为SQLServer2000以上
第二种是采用函数的方法，把对日期的逻辑处理放到函数中，调用起来会简约一下，适合环境为SQLServer2000以上
第三种是采用CTE的方法，用CTE来封装判断逻辑，适合环境为SQLServer2005以上
|
--创建表和数据
CREATE TABLE employees
(
?name????VARCHAR(50),
?birthday DATETIME
)
INSERT INTO employees VALUES('WBQ','1948-12-08');
INSERT INTO employees VALUES('CZH','1952-02-19');
INSERT INTO employees VALUES('LB','1963-08-30');
INSERT INTO employees VALUES('YLL','1937-09-19');
INSERT INTO employees VALUES('YGQ','1955-03-04');
INSERT INTO employees VALUES('CHH','1963-07-02');
INSERT INTO employees VALUES('SWG','1960-05-29');
INSERT INTO employees VALUES('HW','1958-01-01');
INSERT INTO employees VALUES('YY','1972-02-29');
INSERT INTO employees VALUES('LM','1999-02-08');
INSERT INTO employees VALUES('ZY','1972-06-09');
INSERT INTO employees VALUES('WZH','1999-02-28');
SELECT name,birthday FROM employees
?
--普通的SQL实现
SELECT
?name,
?birthday,
?GETDATE() getdate,
?CONVERT(VARCHAR(10),GETDATE(),120) todayVarchar,
?CONVERT(DATETIME,CONVERT(VARCHAR(10),120)) todayDateTime,
?DATEDIFF(YY,birthday,GETDATE()) DateBetween,
?DATEADD(YY,DATEDIFF(YY,GETDATE()),birthday) ThisBirthday,GETDATE())+1,birthday) NextBirthday,
?CASE WHEN CONVERT(DATETIME,120))>DATEADD(YY,birthday)
??????THEN DATEADD(YY,birthday)
??????ELSE DATEADD(YY,birthday)
?END newBirthdayStandard,birthday)???????
??????THEN DATEADD(YY,birthday)+
???????????CASE WHEN DAY(birthday)=29 AND DAY(DATEADD(YY,birthday))=28 THEN 1 ELSE 0 END
??????ELSE DATEADD(YY,birthday) +
???????????CASE WHEN DAY(birthday)=29 AND DAY(DATEADD(YY,birthday))=28 THEN 1 ELSE 0 END
?END newBirthdayForeign?
FROM employees
?
--使用函数来实现
CREATE FUNCTION GetBirthday(@birthday DATETIME,@flag INT)
RETURNS DATETIME
AS
BEGIN
?DECLARE @BirthdayRet DATETIME,@BirthdayThis DATETIME,@BirthdayNext DATETIME,@today DATETIME,@dateBetween INT
?SET @today=CONVERT(DATETIME,120))
?SET @dateBetween=DATEDIFF(YY,@birthday,GETDATE())
?SET @BirthdayThis=DATEADD(YY,@DateBetween,@birthday)????
?SET @BirthdayNext=DATEADD(YY,@DateBetween+1,@birthday)
?IF @flag=1 --2月29日的生日计算为2月28日
?BEGIN
???IF @today>@BirthdayThis
??????SET @BirthdayRet=@BirthdayNext
???ELSE
??????SET @BirthdayRet=@BirthdayThis
?END
?ELSE??????--2月29日的生日计算为3月1日
?BEGIN
???IF @today>@BirthdayThis
??????????IF DAY(@birthday)=29 AND DAY(@BirthdayNext)=28
?????????SET @BirthdayRet=@BirthdayNext+1
??????ELSE
?????????SET @BirthdayRet=@BirthdayNext
???ELSE
??????????IF DAY(@birthday)=29 AND DAY(@BirthdayThis)=28
?????????SET @BirthdayRet=@BirthdayThis+1
??????ELSE
?????????SET @BirthdayRet=@BirthdayThis?END????
?RETURN @BirthdayRet
END
?
SELECT name,dbo.GetBirthday(birthday,0),1) FROM employees
?
--通过CTE来实现
WITH DateBetween AS
(
?SELECT??
???name,
???birthday,
???GETDATE() getdate,
???CONVERT(VARCHAR(10),
???CONVERT(DATETIME,
???DATEDIFF(YY,GETDATE()) DateBetween
?FROM employees
),
DateBirthdayThisAndNext AS
(
?SELECT
???name,getdate,todaydatetime,datebetween,
???DATEADD(YY,DateBetween,birthday) AS DateCur,DateBetween+1,birthday) AS DateNext
?FROM DateBetween
),
DateBirthdayThisAndNextForeign AS
(
?SELECT
???name,
???DateCur,DateNext,
???DateCur+CASE WHEN DAY(birthday)=29 AND DAY(DateCur)=28 THEN 1 ELSE 0 END AS DateCurForeign,
???DateNext+CASE WHEN DAY(birthday)=29 AND DAY(DateNext)=28 THEN 1 ELSE 0 END AS DateNextForeign
?FROM DateBirthdayThisAndNext
),
DateBirthday AS
(
?SELECT
???name,
???CASE WHEN DateCurForeign>=todaydatetime THEN DateCurForeign ELSE DateNextForeign END AS birthDayForeign,
???CASE WHEN DateCur>=todaydatetime THEN DateCur ELSE DateNext END AS birthDayStandard
?FROM DateBirthdayThisAndNextForeign
)
SELECT name,birthDayForeign,birthDayStandard FROM DateBirthday
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
