|
|
|
|
你现在的位置：您现在的位置是: 中国ASP>>数据库>>sql server |
|
| 查找不同时间段间的差异 T－SQL系列 |
|
基础准备：
用随机数生成表。
IF NULLIF(OBJECT_ID(''measurements''),0)>0
DROP TABLE measurements
GO
CREATE TABLE measurements
(
when_taken datetime NOT NULL,
temperature numeric(4,1) --(Fahrenherit)
)
CREATE CLUSTERED INDEX measurements_idx01
ON measurements(when_taken)
GO
DECLARE @counter int,@whendate datetime,@val numeric(4,1),
@randdiff smallint,@randmins smallint
SELECT @counter=1,@whendate=GETDATE(),@val=50.0
/*Insert 20 rows of data.Change constrant if you want more*/
WHILE(@counter<=20)
BEGIN
INSERT measurements valueS(@whendate,@val)
SELECT @randdiff=CASE
WHEN CONVERT(int,RAND()*100)%2=1 THEN
CONVERT(int,RAND()*100)%21*-1
ELSE CONVERT(int,RAND()*100)%21
END,
@randmins=CONVERT(int,RAND()*100000)%10080
--10080 (mins in a week)
SELECT @counter=@counter+1,
@whendate=DATEADD(mi,@randmins,GETDATE()),
@val=@val+@randdiff+RAND()
END
SELECT * FROM measurements
结果：
when_taken temperature
--------------------------- -----------
2002-01-30 20:45:42.610 50.0
2002-01-31 09:48:42.773 63.7
2002-01-31 17:28:42.820 18.0
。。。。。。
方法1：标准的SQL
可用视图或导出表
CREATE VIEW rankdates(when_taken,temperature,daterank)
AS
SELECT when_taken,temperature,
(SELECT COUNT(DISTINCT when_taken) FROM measurements AS T1
WHERE T1.when_taken<=T0.when_taken) AS rank
FROM measurements as T0
GO
SELECT * FROM rankdates ORDER BY daterank
结果：
when_taken temperature daterank
--------------------------- ----------- -----------
2002-01-30 20:45:42.610 50.0 1
2002-01-31 09:48:42.773 63.7 2
2002-01-31 17:28:42.820 18.0 3
。。。。。。
SELECT
P1_WHEN=V1.when_taken,P2_WHEN=V2.when_taken,
P1=V1.temperature,P2=v2.temperature,
DIFF=(v2.temperature-v1.temperature)
FROM rankdates AS V1 LEFT JOIN rankdates AS V2
ON(V2.daterank=V1.daterank+1)
GO
结果：
P1_WHEN P2_WHEN P1 P2 DIFF
--------------------------- --------------------------- ------ ------ --------
2002-01-30 20:45:42.610 2002-01-31 09:48:42.773 50.0 63.7 13.7
2002-01-31 09:48:42.773 2002-01-31 17:28:42.820 63.7 18.0 -45.7
2002-01-31 17:28:42.820 2002-01-31 17:41:42.780 18.0 50.1 32.1
|
|
|
