求一条SQL语句

[yas]

create table TEST (
    cate_name nvarchar(32),
    date0 datetime,
    v float
)

insert into test select 'A','2012-01-01 00:00:00','2.5'
insert into test select 'A','2012-02-03 00:00:00','3.2'
insert into test select 'A','2012-06-05 00:00:00','3.5'
insert into test select 'B','2012-01-01 00:00:00','3.2'
insert into test select 'B','2012-03-05 00:00:00','3.7'
insert into test select 'B','2012-08-03 00:00:00','3.8'

如何写一条语句，返回的结果是
cate_name start_time end_time v
'A' '2012-01-01 00:00:00' '2012-02-03 00:00:00' 2.5
'A' '2012-02-03 00:00:00' '2012-06-05 00:00:00' 3.2
'A' '2012-06-05 00:00:00' null 3.5
'A' '2012-01-01 00:00:00' '2012-03-05 00:00:00' 3.2
'A' '2012-03-05 00:00:00' '2012-08-03 00:00:00' 3.7
'A' '2012-08-03 00:00:00' null 3.8

[kensouterry1]

;WITH c1 AS
(
	SELECT 
		ROW_NUMBER() OVER(PARTITION BY cate_name ORDER BY date0) rowid
		,cate_name
		,date0
		,v
	FROM test
)
SELECT 
	c.cate_name
	,start_time = c.date0
	,end_time = d.date0
	,c.v
FROM c1 c
LEFT JOIN c1 d ON c.rowid + 1 = d.rowid AND c.cate_name = d.cate_name --d>c

cate_name                        start_time              end_time                v
-------------------------------- ----------------------- ----------------------- ----------------------
A                                2012-01-01 00:00:00.000 2012-02-03 00:00:00.000 2.5
A                                2012-02-03 00:00:00.000 2012-06-05 00:00:00.000 3.2
A                                2012-06-05 00:00:00.000 NULL                    3.5
B                                2012-01-01 00:00:00.000 2012-03-05 00:00:00.000 3.2
B                                2012-03-05 00:00:00.000 2012-08-03 00:00:00.000 3.7
B                                2012-08-03 00:00:00.000 NULL                    3.8

[gogodiy]

MSSQL2005及以上版本：

create table t1 (
    cate_name nvarchar(32),
    date0 datetime,
    v float
)
insert into t1 select 'A','2012-01-01 00:00:00','2.5'
insert into t1 select 'A','2012-02-03 00:00:00','3.2'
insert into t1 select 'A','2012-06-05 00:00:00','3.5'
insert into t1 select 'B','2012-01-01 00:00:00','3.2'
insert into t1 select 'B','2012-03-05 00:00:00','3.7'
insert into t1 select 'B','2012-08-03 00:00:00','3.8'
SELECT * FROM t1

;WITH AAA AS
(
	SELECT ROW_NUMBER() OVER(PARTITION BY cate_name ORDER BY date0) AS rowindex,* FROM t1
)
SELECT A.cate_name, A.date0 AS start_time,B.date0 AS end_time,A.v
FROM AAA AS A LEFT JOIN AAA AS B ON A.rowindex=B.rowindex-1
AND A.cate_name=B.cate_name

----------------------------
cate_name	start_time	end_time	v
A	2012-01-01 00:00:00.000	2012-02-03 00:00:00.000	2.5
A	2012-02-03 00:00:00.000	2012-06-05 00:00:00.000	3.2
A	2012-06-05 00:00:00.000	NULL	3.5
B	2012-01-01 00:00:00.000	2012-03-05 00:00:00.000	3.2
B	2012-03-05 00:00:00.000	2012-08-03 00:00:00.000	3.7
B	2012-08-03 00:00:00.000	NULL	3.8

[yas]

其实自己刚发完贴就写出来了 ，一时脑子没开窍

[yas]

写错了，结果应该是 cate_name start_time end_time v 'A' '2012-01-01 00:00:00' '2012-02-03 00:00:00' 2.5 'A' '2012-02-03 00:00:00' '2012-06-05 00:00:00' 3.2 'A' '2012-06-05 00:00:00' null 3.5 'B' '2012-01-01 00:00:00' '2012-03-05 00:00:00' 3.2 'B' '2012-03-05 00:00:00' '2012-08-03 00:00:00' 3.7 'B' '2012-08-03 00:00:00' null 3.8

[asd100230520]

我也想知道 只能帮顶