使用in和Between

Question

SELECT TOP 5 Notices.Id, NoticeL.Notices_Id, Loc.Id as Location_Id,
CAST(Notices.Text AS TEXT) as Text, CAST(Notices.Title AS TEXT) as Title,
Notices.CDate as RegDate 

FROM NoticeL JOIN Notices ON NoticeL.Notices_Id=Notices.Id
JOIN Loc ON NoticeL.Loc_Id=Loc.Id
WHERE Loc_Id IN (1) BETWEEN '06/04/2012' AND '23/04/2012'

我正试着在跟随IN之间使用，但我没有运气。我得到以下语法错误：

   Msg 156, Level 15, State 1, Line 1
   Incorrect syntax near the keyword 'BETWEEN'.

我猜MsSql不喜欢它的语法。我该怎么做呢？

第二个问题，我想把过去两周的通知过滤掉。有没有办法我可以在mssql中动态地做这件事。谢谢你的帮助。

Answer 1

 SELECT TOP 5 Notices.Id,
              NoticeL.Notices_Id, 
              Loc.Id as Location_Id,
              CAST(Notices.Text AS TEXT) as Text,
              CAST(Notices.Title AS TEXT) as Title,
              Notices.CDate as RegDate
 FROM NoticeL 
 JOIN Notices ON NoticeL.Notices_Id=Notices.Id JOIN Loc ON NoticeL.Loc_Id=Loc.Id
 WHERE Loc_Id IN (1) 
 AND Notices.CDate BETWEEN '06/04/2012' AND '23/04/2012'

您不能在同一字段中组合IN和BETWEEN。我猜需要在Notices.CDate上使用Between，因为这似乎是唯一的日期字段。

如果您想获取最近两周的价值，那么最后一行代码更改为

 AND Notices.CDate BETWEEN GETDATE() - 14 AND GETDATE()

如果查询中时间很重要，那么您可能想要做些什么来从GETDATE()中剥离时间。这个问题似乎有一些很好的答案。

How to return the date part only from a SQL Server datetime datatype

Answer 2

NoticeL.Loc_Id=Loc.Id WHERE Loc_Id = 1 -- IN (1) should work too
                                      -- if you're building the query 
                                      -- dynamically as a string and want 
                                      -- to use IN with a list of IDs 
AND Notices.CDate BETWEEN '06/04/2012' AND '23/04/2012

在过去的两周(也就是最后14个日历天)，你可以

AND Notices.CDate BETWEEN DATEADD(d,-14,GETDATE()) AND GETDATE()

Answer 3

我想你错过了，试试这个

FROM NoticeL JOIN Notices ON NoticeL.Notices_Id=Notices.Id
JOIN Loc ON NoticeL.Loc_Id=Loc.Id
WHERE Loc_Id IN (1) AND BETWEEN '06/04/2012' AND '23/04/2012'