关于SQLServer2005的学习笔记——生日问题

生日问题是个看似简单逻辑上却又比较复杂的小问题

主要的逻辑难点第一个是关于闰月尾天的计算，第二个是判断本年度生日是否已过

本文给出了三种解决办法，

第一种是最常用的解决办法，即常用的 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),GETDATE(),120)) todayDateTime,

DATEDIFF(YY,birthday,GETDATE()) DateBetween,

DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday) ThisBirthday,

DATEADD(YY,DATEDIFF(YY,birthday,GETDATE())+1,birthday) NextBirthday,

CASE WHEN CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120))>DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday)

THEN DATEADD(YY,DATEDIFF(YY,birthday,GETDATE())+1,birthday)

ELSE DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday)

END newBirthdayStandard,

CASE WHEN CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120))>DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday)

THEN DATEADD(YY,DATEDIFF(YY,birthday,GETDATE())+1,birthday)+

CASE WHEN DAY(birthday)=29 AND DAY(DATEADD(YY,DATEDIFF(YY,birthday,GETDATE())+1,birthday))=28 THEN 1 ELSE 0 END

ELSE DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday) +

CASE WHEN DAY(birthday)=29 AND DAY(DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),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,CONVERT(VARCHAR(10),GETDATE(),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,birthday,dbo.GetBirthday(birthday,0),dbo.GetBirthday(birthday,1) FROM employees

-- 通过 CTE 来实现

WITH DateBetween AS

(

SELECT

name,

birthday,

GETDATE() getdate,

CONVERT(VARCHAR(10),GETDATE(),120) todayVarchar,

CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120)) todayDateTime,

DATEDIFF(YY,birthday,GETDATE()) DateBetween

FROM employees

),

DateBirthdayThisAndNext AS

(

SELECT

name,birthday,getdate,todaydatetime,datebetween,

DATEADD(YY,DateBetween,birthday) AS DateCur,

DATEADD(YY,DateBetween+1,birthday) AS DateNext

FROM DateBetween

),

DateBirthdayThisAndNextForeign AS

(

SELECT

name,birthday,todaydatetime,

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,birthday,

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,birthday,birthDayForeign,birthDayStandard FROM DateBirthday