很少研究SQL，不过其实它本身还是可以解决很多问题。
刚才花了点时间用SQL解决了一个这样的问题：
做一张按周统计的月报表，如果一周里面有3个工作日是这个月的，则在本月报表中统计，统计这样的周时需要把属于该周其它月的那几天也统计进来。写出来的SQL如下：
declare @month int
declare @theFirstDate datetime
declare @theEndDate datetime
declare @firstMonday datetime
— @month is defined to get the calc month of current year
set @month=4
–get '2008-04-01'
set @theFirstDate = DATEADD(mm, @month-1, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
–get '2008-05-01'
set @theEndDate = DATEADD(mm, @month, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
–get the first monday of every month (can be a weekday of last month), ex: '2008-03-31'
set @firstMonday = DATEADD(wk, DATEDIFF(wk,0,@theFirstDate), 0)
–if in the first week there are more than 3 days belong to the month, the first week will be count
If DATEDIFF(dd, @firstMonday, @theFirstDate) < 3 AND DATEDIFF(dd, @firstMonday, @theFirstDate) >0
Begin
— get the time period, and calc the report, ex: 2008-03-31 ~ 2008-04-04
select CAST(@firstMonday AS CHAR) + CAST(DATEADD(wk, 1, @firstMonday) AS CHAR)
End
— the next monday, ex: 2008-04-07
set @firstMonday = DATEADD(wk, 1, @firstMonday)
— if datediff between the next monday and the first day of next month larger than 2, this week will be count
While DATEDIFF(dd, @firstMonday, @theEndDate) >= 3
Begin
— get the time period, and calc the report
select CAST(@firstMonday AS CHAR) + CAST(DATEADD(wk, 1, @firstMonday) AS CHAR)
— the next monday, ex: 2008-04-07
set @firstMonday = DATEADD(wk, 1, @firstMonday)
End
最近在看《编程之美》，觉得这个应该也可以是道不错的面试题。