表结构:
InfoTable: ID,Name...
DownCount: ID,InfoID,CountDate,DownTimesselect 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高人帮忙优化.当然了, 在项目中这种数据我肯定会缓存起来不会次次去数据库取的.
InfoTable: ID,Name...
DownCount: ID,InfoID,CountDate,DownTimesselect 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高人帮忙优化.当然了, 在项目中这种数据我肯定会缓存起来不会次次去数据库取的.
解决方案 »
- 跪求写法··
- CheckBoxList的用法.如何取值
- vicqqq 看看这个 .......................1分都没了
- 郁闷,母版页为什么会这样?怎么在使用了母版页的情况下使用js?大家帮忙
- 这个存储过程分页的代码,怎么返回总记录数?
- 高分求救:asp.net远程控制局域网电脑
- 怎么做一个用XML文件做数据库的留言本
- 怎么实现本地打印呢?
- 如何在datagrid中添加行?
- 如何更改DataSet
- 【元宵节*分享】文件上传 带进度条(多种风格)
- ======================有会Linq的么???Linq区死水到这来问,《 Linq To DataTable》===============
where hittime between dateadd(dd,-7,gedate()) and getdate()
group by [id]
select *
from tb
--order by DATEPART(month,时间字段)--按月
--order by DATEPART(week,时间字段)--按周
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这个写法不知道跟你的比那个快,你测试一下看看
另外可以使用视图或表来时保存历史处理数据,速度当然好些
where hittime between dateadd(dd,-7,gedate()) and getdate()
group by [id]
select *
from tb就这方法就行,如果数据不好拼接,你用一个临时表拼一下,再取。