解决方案 »
- 如何使删除操作不记录在日志?
- 如何知道sqlserver中某个varchar字段中的数据含有几行
- 每一次用到动态SQL语句,写成这样,不出错,也没有得到数据集?请高手指点!
- 怎么會出現server registration information cannot be changed on remote registries?
- 用存储过程往sql里写数据时,为什么会自动把字符型数据前面的零给去掉
- SQL筛选求高人指点
- 请问一下Stored Procedures In的用法
- 如何用Excel表中的一列替换数据库表中的一列?
- 怎么从数据库里随机抽取记录?
- dw的update语句set部分为何总用所有字段?
- sql select 查询操作高级用法实现
- 动态生成sql语句的问题
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([DateTime] Datetime,[Value] decimal(18,3))
Insert #T
select '2011-11-02 09:01:30.000',321.600 union all
select '2011-11-02 09:01:00.000',313.300 union all
select '2011-11-02 09:00:30.000',293.300 union all
select '2011-11-02 09:00:00.000',279.300 union all
select '2011-11-02 08:59:30.000',278.700 union all
select '2011-11-02 08:59:00.000',278.800 union all
select '2011-11-02 08:58:30.000',279.800 union all
select '2011-11-02 08:58:00.000',279.600 union all
select '2011-11-02 08:57:30.000',284.100 union all
select '2011-11-02 08:57:00.000',301.300 union all
select '2011-11-02 08:56:30.000',314.200 union all
select '2011-11-02 08:56:00.000',314.300 union all
select '2011-11-02 08:55:30.000',313.600 union all
select '2011-11-02 08:55:00.000',301.200 union all
select '2011-11-02 08:54:30.000',280.200 union all
select '2011-11-02 08:54:00.000',274.000 union all
select '2011-11-02 08:53:30.000',273.900 union all
select '2011-11-02 08:53:00.000',296.100 union all
select '2011-11-02 08:52:30.000',312.300 union all
select '2011-11-02 08:52:00.000',313.700 union all
select '2011-11-02 08:51:30.000',314.200 union all
select '2011-11-02 08:51:00.000',317.600 union all
select '2011-11-02 08:50:30.000',320.200 union all
select '2011-11-02 08:50:00.000',320.700
Go
DECLARE @dt DATETIME,@dt2 DATETIME
SET @dt='2011-11-02 08:50'--開始時間
SELECT @dt2=MAX([DateTime]) FROM #T;WITH a
AS
(
Select DATEADD(n,number*5,@dt)AS STARTTime,DATEADD(n,number*5+5,@dt) AS EndTime
from master.dbo.spt_values AS b
WHERE b.type='P' AND DATEADD(n,number*5,@dt)<@dt2
)
SELECT
a.STARTTime,AVG([Value]) AS [AvgValue]
FROM a,#T AS b
WHERE b.[DateTime]>=a.STARTTime AND b.[DateTime]<EndTime
GROUP BY a.STARTTime/*
STARTTime AvgValue
2011-11-02 08:50:00.000 302.290000
2011-11-02 08:55:00.000 294.560000
2011-11-02 09:00:00.000 301.875000
*/
insert into tb select '2011-11-02 09:01:30.000',321.600
insert into tb select '2011-11-02 09:01:00.000',313.300
insert into tb select '2011-11-02 09:00:30.000',293.300
insert into tb select '2011-11-02 09:00:00.000',279.300
insert into tb select '2011-11-02 08:59:30.000',278.700
insert into tb select '2011-11-02 08:59:00.000',278.800
insert into tb select '2011-11-02 08:58:30.000',279.800
insert into tb select '2011-11-02 08:58:00.000',279.600
insert into tb select '2011-11-02 08:57:30.000',284.100
insert into tb select '2011-11-02 08:57:00.000',301.300
insert into tb select '2011-11-02 08:56:30.000',314.200
insert into tb select '2011-11-02 08:56:00.000',314.300
insert into tb select '2011-11-02 08:55:30.000',313.600
insert into tb select '2011-11-02 08:55:00.000',301.200
insert into tb select '2011-11-02 08:54:30.000',280.200
insert into tb select '2011-11-02 08:54:00.000',274.000
insert into tb select '2011-11-02 08:53:30.000',273.900
insert into tb select '2011-11-02 08:53:00.000',296.100
insert into tb select '2011-11-02 08:52:30.000',312.300
insert into tb select '2011-11-02 08:52:00.000',313.700
insert into tb select '2011-11-02 08:51:30.000',314.200
insert into tb select '2011-11-02 08:51:00.000',317.600
insert into tb select '2011-11-02 08:50:30.000',320.200
insert into tb select '2011-11-02 08:50:00.000',320.700
go
select dt,avg(value)value from(
select convert(varchar(15),dt,120)+ltrim(substring(convert(varchar(18),dt,120),16,1)/5*5)dt,value from tb
)t group by dt
/*
dt value
--------------------------- ---------------------------------------
2011-11-02 08:50 302.290000
2011-11-02 08:55 294.560000
2011-11-02 09:00 301.875000(3 行受影响)*/
go
drop table tb
create table t2(DateTimes datetime, value float)insert into t2
select '2011-11-02 09:01:30.000', 321.600 union all
select '2011-11-02 09:01:00.000', 313.300 union all
select '2011-11-02 09:00:30.000', 293.300 union all
select '2011-11-02 09:00:00.000', 279.300 union all
select '2011-11-02 08:59:30.000', 278.700 union all
select '2011-11-02 08:59:00.000', 278.800 union all
select '2011-11-02 08:58:30.000', 279.800 union all
select '2011-11-02 08:58:00.000', 279.600 union all
select '2011-11-02 08:57:30.000', 284.100 union all
select '2011-11-02 08:57:00.000', 301.300 union all
select '2011-11-02 08:56:30.000', 314.200 union all
select '2011-11-02 08:56:00.000', 314.300 union all
select '2011-11-02 08:55:30.000', 313.600 union all
select '2011-11-02 08:55:00.000', 301.200 union all
select '2011-11-02 08:54:30.000', 280.200 union all
select '2011-11-02 08:54:00.000', 274.000 union all
select '2011-11-02 08:53:30.000', 273.900 union all
select '2011-11-02 08:53:00.000', 296.100 union all
select '2011-11-02 08:52:30.000', 312.300 union all
select '2011-11-02 08:52:00.000', 313.700 union all
select '2011-11-02 08:51:30.000', 314.200 union all
select '2011-11-02 08:51:00.000', 317.600 union all
select '2011-11-02 08:50:30.000', 320.200 union all
select '2011-11-02 08:50:00.000', 320.700with
aa as
(select *,datediff(mi,(select min(datetimes) from t2),datetimes) dfa from t2),
bb as
(select *,dfa+(5-dfa%5) dfb from aa)
select min(Datetimes) 'DateTimes',avg(value) 'Value'
from bb
group by dfb
order by min(Datetimes) DateTimes Value
----------------------- ----------------------
2011-11-02 08:50:00.000 302.29
2011-11-02 08:55:00.000 294.56
2011-11-02 09:00:00.000 301.875