表结构:
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高人帮忙优化.当然了, 在项目中这种数据我肯定会缓存起来不会次次去数据库取的.
解决方案 »
- 不用input type="file“ 如何打开文件窗口?
- 问下关于GridView1点编辑后模板列赋值问题
- 求一正则表达式,只能输入汉字,数字,字母。谢谢。【F5死循环中。。。】
- 如何一次性删除多个SQL2005的存储过程(图形操作和SQL语句都可以)?
- asp.net2.0 用户管理方面的问题,高手进来看看,谢谢!
- 怎么在聊天室里面加表情
- 求助!c#中关于split,ubound的用用法?
- 如何设置当前页面的得到焦点的textbox的值?
- 你们如何控制对admin目录的访问的?
- 给分
- 【元宵节*分享】文件上传 带进度条(多种风格)
- ======================有会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就这方法就行,如果数据不好拼接,你用一个临时表拼一下,再取。