悠悠博客 关注Ajax技术与web开发

基础准备：

用随机数生成表。

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