关于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