表:date 字段表示时间。
num date
3 2013-07-01 00:00:00
5 2013-07-01 00:01:01
1 2013-07-01 00:04:30
3 2013-07-01 00:10:40
7 2013-07-01 00:12:00
8 2013-07-01 00:15:43
3 2013-07-01 00:22:00需要:从现在开始,往前追溯,每各1个小时计算num字段的和
num date
3 2013-07-01 00:00:00
5 2013-07-01 00:01:01
1 2013-07-01 00:04:30
3 2013-07-01 00:10:40
7 2013-07-01 00:12:00
8 2013-07-01 00:15:43
3 2013-07-01 00:22:00需要:从现在开始,往前追溯,每各1个小时计算num字段的和
解决方案 »
- 是不是必须先求得各ID的Max(Val),再将其作为定位条件从表ValTab中提取出最大值对应的记录?
- 我有个存储过程里面这样exec(mydata),就是组装出来的数据显示怎么格式化呢?
- 急!!如何在新建SQLServer组中注册产生项目
- 我们自己用VB+(SQL SERVER 2000)或MSDE编写了单机版应用程序交给客户使用,同时也必须将mdf数据库一起交给客户,假设我们让很多用户都
- sql SERVER 问题请教
- 小问题:数据库里的字段类型是binary(20),我怎么把它显示出来??
- 请问如何在LINUX下安装MSSQL
- 存储过程通用分页怎么分页后速度这么慢,以下是通用存储过程
- FOXPRo直接往打印机送数据
- 我的sqlserver 7.0出错了,一个怪问题。
- 急求!数据库升级
- 求SQL语句写法
with tb(num,date)as(
select 3,'2013-07-01 01:00:00' union
select 5,'2013-07-01 02:01:01' union
select 1,'2013-07-01 03:04:30' union
select 3,'2013-07-01 05:10:40' union
select 7,'2013-07-01 07:12:00' union
select 8,'2013-07-01 09:15:43' union
select 3,'2013-07-01 16:22:00'
)
,tc as(
select number,num from tb right join master..spt_values a
on DATEPART(hh,date)=a.number
where type='p' and number between 0 and 23
)
select b.number,isnull(SUM(a.num),0) from tc a,tc b where a.number<=b.number
group by b.number
order by 1这种效果?
(num int identity(1,1),[date] datetime)insert into t([date])values(getdate())
insert into t([date])values(getdate())
insert into t([date])values(getdate())
insert into t([date])values(getdate())
insert into t([date])values(dateadd(dd,-1,getdate()))
insert into t([date])values(dateadd(dd,-1,getdate()))
insert into t([date])values(dateadd(dd,-1,getdate()))select cast(YEAR([date]) as varchar)+'-'+cast(MONTH([date]) as varchar)+'-'+cast(DAY([date]) as varchar) as [date],SUM(num)
from t
group by YEAR([date]),MONTH([date]),DAY([date])
select conver(char(14),日期字段,120),sum(num)
from table1
group by conver(char(14),日期字段,120)
order by conver(char(14),日期字段,120) desc
from t
group by DATEDIFF(HH,[date],getdate())