求一条SQL语句

Question

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

Answer 1

写错了，结果应该是
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

Answer 2

;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>ccate_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

Answer 3

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