关于SQLServer2005的学习笔记――生日问题
发布时间：2020-12-12 15:25:13 所属栏目：MsSql教程 来源：网络整理
导读：生日问题是个看似简单逻辑上却又比较复杂的小问题 ? 主要的逻辑难点第一个是关于闰月尾天的计算，第二个是判断本年度生日是否已过 ? 本文给出了三种解决办法， 第一种是最常用的解决办法，即常用的 SQL 语法，不过看起来比较复杂，适合环境为 SQLServer2000
|
生日问题是个看似简单逻辑上却又比较复杂的小问题
?
主要的逻辑难点第一个是关于闰月尾天的计算，第二个是判断本年度生日是否已过
?
本文给出了三种解决办法，
第一种是最常用的解决办法，即常用的 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 ? （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |