关于统计周排行与月排行的SQL语句, 求SQL高人帮忙优化.

[wapit]

表结构:
InfoTable: ID,Name...
DownCount: ID,InfoID,CountDate,DownTimes

select ID,Name,
ISNULL((select SUM(DownTimes) from DownCount 
where InfoID=InfoTable.ID and DATEDIFF(d,CountDate,GetDate())<=7),0)
as WeekDowns,
ISNULL((select SUM(DownTimes) from DownCount 
where InfoID=InfoTable.ID and DATEDIFF(d,CountDate,GetDate())<=30),0)
as MonthDowns
from InfoTable
order by MonthDowns desc

结果:
ID, Name,WeekDowns,MonthDowns

这样取当然没有问题. 但我觉得这方法有些笨, 效率估计不高, 求SQL高人帮忙优化.

当然了, 在项目中这种数据我肯定会缓存起来不会次次去数据库取的.

[seeQe]

看看 sql server 的帮助语法,其中有相关函数的,
另外可以使用视图或表来时保存历史处理数据,速度当然好些

[happy664618843]

引用 1 楼 wuyq11 的回复:

select [id],count(*) from tb
where hittime between dateadd(dd,-7,gedate()) and getdate()
group by [id]
select *
from tb
--order by DATEPART(month,时间字段)--按月
--order by DATEPART(week,时间字段)--按周

。。。

[wuyq11]

select [id],count(*) from tb
where hittime between dateadd(dd,-7,gedate()) and getdate()
group by [id]
select *
from tb
--order by DATEPART(month,时间字段)--按月
--order by DATEPART(week,时间字段)--按周

[灵雨飘零]

引用 1 楼 wuyq11 的回复:

select [id],count(*) from tb
where hittime between dateadd(dd,-7,gedate()) and getdate()
group by [id]
select *
from tb
--order by DATEPART(month,时间字段)--按月
--order by DATEPART(week,时间字段)--按周

up

[liuyjcel]

引用 1 楼 wuyq11 的回复:

select [id],count(*) from tb
where hittime between dateadd(dd,-7,gedate()) and getdate()
group by [id]
select *
from tb
--order by DATEPART(month,时间字段)--按月
--order by DATEPART(week,时间字段)--按周

可以借鉴

[gongsun]

...

回复内容太短了！

[随风落梦]

引用 1 楼 wuyq11 的回复:

select [id],count(*) from tb
where hittime between dateadd(dd,-7,gedate()) and getdate()
group by [id]
select *
from tb
--order by DATEPART(month,时间字段)--按月
--order by DATEPART(week,时间字段)--按周

up

[wapit]

引用 1 楼 wuyq11 的回复:

select [id],count(*) from tb
where hittime between dateadd(dd,-7,gedate()) and getdate()
group by [id]
select *
from tb
--order by DATEPART(month,时间字段)--按月
--order by DATEPART(week,时间字段)--按周

我想一次查出来......

[whb147]

我晕，一次查出来那就要left join le

[whb147]

还有一个问题
就是是自然周第一周，第二周……，自然月1月，2月，3月……？
还是就是当前天往前7，为一周，往前30天为一月？

[MarcuseXiao]

如果是访问统计数据比较频繁，建议修改一下设计，生成相应的统计表。这样肯定快。不管你用什么语句，总是要扫描多条数据记录，肯定不会快的。

[whb147]

select a.ID,a.Name,isnull(b.WeekDowns,0) as WeekDowns,isnull(c.MonthDowns,0) as MonthDowns
from 
InfoTable a
left join (select InfoID,sum(DownTimes ) as WeekDowns from DownCount b where DATEDIFF(d,CountDate,GetDate())<=7 group by InfoID) b on a.id=b.InfoID 
left join (select InfoID,sum(DownTimes ) as MonthDowns from DownCount b where DATEDIFF(d,CountDate,GetDate())<=30 group by InfoID) c on a.id=c.InfoID 

order by MonthDowns desc

这个写法不知道跟你的比那个快，你测试一下看看

[林小白]

select [id],count(*) from tb
where hittime between dateadd(dd,-7,gedate()) and getdate()
group by [id]
select *
from tb

就这方法就行，如果数据不好拼接，你用一个临时表拼一下，再取。