【转】关于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

 

转载于:https://www.cnblogs.com/nnugaozhi/archive/2010/02/23/1671744.html