SQL语句DATEPART帮助

Question

这条SQL语句有什么问题？我一直得到SelMonth和CurMonth是无效的列，我的Where子句写错了吗？是DATEPART在这里引起了问题吗？

SELECT TOP (5) 
   EName, EDate, EDateEnd, 
   DATEPART(month, EDate) AS SelMonth, 
   DATEPART(month, { fn CURDATE() }) AS CurMonth
FROM         
   Events
WHERE     
   (EDate >= { fn CURDATE() }) 
   AND (SelMonth = CurMonth)

谢谢

Answer 1

不能在WHERE子句中使用SelMonth和CurMonth：

SELECT TOP (5) 
       EName, EDate, EDateEnd, 
       DATEPART(month, EDate) AS SelMonth, 
       DATEPART(month, { fn CURDATE() }) AS CurMonth
FROM   Events
WHERE  (EDate >= { fn CURDATE() }) 
       AND (DATEPART(month, EDate) = DATEPART(month, { fn CURDATE() }))

Answer 2

由于SelMonth和CurMonth是派生列，因此它们在当前级别的查询中不可用。选项1: CTE (适用于SQL 2005及更高版本)

WITH evnts AS
(SELECT ename, 
        edate, 
        edateend, 
        Datepart(MONTH, edate)            AS selmonth, 
        Datepart(MONTH, { fn CURDATE() }) AS curmonth 
 FROM   events)
SELECT TOP (5) * 
  FROM  evnts
 WHERE   (EDate >= { fn CURDATE() }) AND (SelMonth = CurMonth) 

选项2:内联视图

SELECT TOP (5) * FROM
(SELECT ename, 
        edate, 
        edateend, 
        Datepart(MONTH, edate)            AS selmonth, 
        Datepart(MONTH, { fn CURDATE() }) AS curmonth 
 FROM   events)

WHERE     (EDate >= { fn CURDATE() }) AND (SelMonth = CurMonth)