我有一表(记录数上W级别)
部分字段为: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
但效率好象很低,谁给个高点效率的啊??
部分字段为: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
但效率好象很低,谁给个高点效率的啊??
select top 5 Protection,count(Protection) as numProtection,max([time]) as newTime from Protection t1,
(select Protection from Protection where [time] between datediff(mm,-3,GETDATE()) and GETDATE() group by Protection having count(*) > 50 )t2
where t1.Protection = t2.Protection
group by t1.Protection
order by count(Protection) desc