基礎准備：
用隨機數生成表。
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
方法2：先具體化等級，然後自聯結
標准的SQL方法進行了84次的邏輯讀；而臨時表方法在創建表時只進行了40次邏輯讀，然後用52個邏輯讀來訪問它。由於涉及到的數據如此少，所以這兩種方法都只發生一次物理讀。
CREATE TABLE #rankdates(
when_taken datetime,
temperature numeric(4,1),
daterank int IDENTITY PRIMARY KEY)
GO
INSERT #rankdates(when_taken,temperature)
SELECT when_taken,temperature
FROM measurements
ORDER BY when_taken ASC
GO
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
DROP TABLE #rankdates
結果同上。
