基础准备：

用随机数生成表。

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