select * from tablename where datediff(month,[time],getdate())=3
select * from 表 where [tim] beteen datediff(mm,-3,GETDATE()) and GETDATE()
select * from tablename where datediff(month,[time],getdate())=3 union select * from tablename where [time]=getdate()
where [tim] beteen dateadd(m, -3, getdate()) and getdate()
我有一表 字段为:Protection, Version, [time] 内容为: A 1.0 2006-4-5 A 3.0.21 A 4.0.1 A 5.00.0.0 B 1.0 B 3.0.21 B 4.0.1 B 8.00.0.0............. 我想得到当前时间3个月内Protection记录(大于50)最多的前5位Protection,并一并列出Protection最新插入的时间(更进一步。Version版本最高的时间) 我写的如下:但效率好象很低,谁给个高点效率的啊?? select top 5 Protection,count(Protection) as numProtection,max([time]) as newTime from Protection where Protection in (select Protection from Protection where [time] between datediff(mm,-3,GETDATE()) and GETDATE() group by Protection having count(*) > 50 ) group by Protection order by count(Protection) desc
from 表
where [tim] beteen datediff(mm,-3,GETDATE()) and GETDATE()
union
select * from tablename where [time]=getdate()
字段为:Protection, Version, [time]
内容为:
A 1.0 2006-4-5
A 3.0.21
A 4.0.1
A 5.00.0.0
B 1.0
B 3.0.21
B 4.0.1
B 8.00.0.0.............
我想得到当前时间3个月内Protection记录(大于50)最多的前5位Protection,并一并列出Protection最新插入的时间(更进一步。Version版本最高的时间)
我写的如下:但效率好象很低,谁给个高点效率的啊??
select top 5 Protection,count(Protection) as numProtection,max([time]) as newTime from Protection where Protection in (select Protection from Protection where [time] between datediff(mm,-3,GETDATE()) and GETDATE() group by Protection having count(*) > 50 ) group by Protection order by count(Protection) desc