牛首山下

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

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/baoqiangwang/archive/2010/02/21/5314808.aspx

作者：人生一场醉 (用户名:baoqiangwang)

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

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

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

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

第一种是最常用的解决办法，即常用的 SQL 语法，不过看起来比较复杂，适合环境为 SQLServer2000 以上

第二种是采用函数的方法，把对日期的逻辑处理放到函数中，调用起来会简约一下，适合环境为 SQLServer2000 以上

第三种是采用 CTE 的方法，用 CTE 来封装判断逻辑，适合环境为 SQLServer2005 以上

 

 

 1 -- 创建表和数据 
 2  
 3  CREATE TABLE employees 
 4 
 5 ( 
 6 
 7   name     VARCHAR(50), 
 8 
 9   birthday DATETIME 
10 
11 ) 
12 
13  INSERT INTO employees VALUES('WBQ','1948-12-08'); 
14 
15  INSERT INTO employees VALUES('CZH','1952-02-19'); 
16 
17  INSERT INTO employees VALUES('LB','1963-08-30'); 
18 
19  INSERT INTO employees VALUES('YLL','1937-09-19'); 
20 
21 INSERT INTO employees VALUES('YGQ','1955-03-04'); 
22 
23 INSERT INTO employees VALUES('CHH','1963-07-02'); 
24 
25 INSERT INTO employees VALUES('SWG','1960-05-29'); 
26 
27 INSERT INTO employees VALUES('HW','1958-01-01'); 
28 
29 INSERT INTO employees VALUES('YY','1972-02-29'); 
30 
31 INSERT INTO employees VALUES('LM','1999-02-08'); 
32 
33 INSERT INTO employees VALUES('ZY','1972-06-09'); 
34 
35 INSERT INTO employees VALUES('WZH','1999-02-28'); 
36 
37 SELECT name,birthday FROM employees 
38

 

 1 -- 普通的 SQL 实现 
 2 
 3 SELECT 
 4 
 5   name, 
 6 
 7   birthday, 
 8 
 9   GETDATE() getdate, 
10 
11   CONVERT(VARCHAR(10),GETDATE(),120) todayVarchar, 
12 
13   CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120)) todayDateTime, 
14 
15   DATEDIFF(YY,birthday,GETDATE()) DateBetween, 
16 
17   DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday) ThisBirthday, 
18 
19   DATEADD(YY,DATEDIFF(YY,birthday,GETDATE())+1,birthday) NextBirthday, 
20 
21   CASE WHEN CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120))>DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday) 
22 
23        THEN DATEADD(YY,DATEDIFF(YY,birthday,GETDATE())+1,birthday) 
24 
25        ELSE DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday) 
26 
27   END newBirthdayStandard, 
28 
29   CASE WHEN CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120))>DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday)        
30 
31        THEN DATEADD(YY,DATEDIFF(YY,birthday,GETDATE())+1,birthday)+ 
32 
33             CASE WHEN DAY(birthday)=29 AND DAY(DATEADD(YY,DATEDIFF(YY,birthday,GETDATE())+1,birthday))=28 THEN 1 ELSE 0 END 
34 
35        ELSE DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday) + 
36 
37             CASE WHEN DAY(birthday)=29 AND DAY(DATEADD(YY,DATEDIFF(YY,birthday,GETDATE()),birthday))=28 THEN 1 ELSE 0 END 
38 
39   END newBirthdayForeign  
40 
41 FROM employees 
42

 

 1 -- 使用函数来实现 
 2 
 3 CREATE FUNCTION GetBirthday(@birthday DATETIME,@flag INT) 
 4 
 5 RETURNS DATETIME 
 6 
 7 AS 
 8 
 9 BEGIN 
10 
11   DECLARE @BirthdayRet DATETIME,@BirthdayThis DATETIME,@BirthdayNext DATETIME,@today DATETIME,@dateBetween INT 
12 
13   SET @today=CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120)) 
14 
15   SET @dateBetween=DATEDIFF(YY,@birthday,GETDATE()) 
16 
17   SET @BirthdayThis=DATEADD(YY,@DateBetween,@birthday)     
18 
19   SET @BirthdayNext=DATEADD(YY,@DateBetween+1,@birthday) 
20 
21   IF @flag=1 --2 月 29 日的生日计算为 2 月 28 日 
22 
23   BEGIN 
24 
25     IF @today>@BirthdayThis 
26 
27        SET @BirthdayRet=@BirthdayNext 
28 
29     ELSE 
30 
31        SET @BirthdayRet=@BirthdayThis 
32 
33   END 
34 
35   ELSE       --2 月 29 日的生日计算为 3 月 1 日 
36 
37   BEGIN 
38 
39     IF @today>@BirthdayThis 
40 
41             IF DAY(@birthday)=29 AND DAY(@BirthdayNext)=28 
42 
43           SET @BirthdayRet=@BirthdayNext+1 
44 
45        ELSE 
46 
47           SET @BirthdayRet=@BirthdayNext 
48 
49     ELSE 
50 
51             IF DAY(@birthday)=29 AND DAY(@BirthdayThis)=28 
52 
53           SET @BirthdayRet=@BirthdayThis+1 
54 
55        ELSE 
56 
57           SET @BirthdayRet=@BirthdayThis  END     
58 
59   RETURN @BirthdayRet 
60 
61 END 
62 
63   
64 
65 SELECT name,birthday,dbo.GetBirthday(birthday,0),dbo.GetBirthday(birthday,1) FROM employees 
66

 

 1 -- 通过 CTE 来实现 
 2 
 3 WITH DateBetween AS 
 4 
 5 ( 
 6 
 7   SELECT   
 8 
 9     name, 
10 
11      birthday, 
12 
13     GETDATE() getdate, 
14 
15     CONVERT(VARCHAR(10),GETDATE(),120) todayVarchar, 
16 
17     CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120)) todayDateTime, 
18 
19     DATEDIFF(YY,birthday,GETDATE()) DateBetween 
20 
21   FROM employees 
22 
23 ), 
24 
25 DateBirthdayThisAndNext AS 
26 
27 ( 
28 
29   SELECT 
30 
31     name,birthday,getdate,todaydatetime,datebetween, 
32 
33     DATEADD(YY,DateBetween,birthday) AS DateCur, 
34 
35     DATEADD(YY,DateBetween+1,birthday) AS DateNext 
36 
37   FROM DateBetween 
38 
39 ), 
40 
41 DateBirthdayThisAndNextForeign AS 
42 
43 ( 
44 
45   SELECT 
46 
47     name,birthday,todaydatetime, 
48 
49     DateCur,DateNext, 
50 
51     DateCur+CASE WHEN DAY(birthday)=29 AND DAY(DateCur)=28 THEN 1 ELSE 0 END AS DateCurForeign, 
52 
53     DateNext+CASE WHEN DAY(birthday)=29 AND DAY(DateNext)=28 THEN 1 ELSE 0 END AS DateNextForeign 
54 
55   FROM DateBirthdayThisAndNext 
56 
57 ), 
58 
59 DateBirthday AS 
60 
61 ( 
62 
63   SELECT 
64 
65     name,birthday, 
66 
67     CASE WHEN DateCurForeign>=todaydatetime THEN DateCurForeign ELSE DateNextForeign END AS birthDayForeign, 
68 
69     CASE WHEN DateCur>=todaydatetime THEN DateCur ELSE DateNext END AS birthDayStandard 
70 
71   FROM DateBirthdayThisAndNextForeign 
72 
73 ) 
74 
75 SELECT name,birthday,birthDayForeign,birthDayStandard FROM DateBirthday 
76

 

posted on 2010-02-23 10:27  牛首山下  阅读(353)  评论(0)    收藏  举报

导航