求一sql,谢谢！

[homel]

实例如下：
table a
stepid step_sequence step_name
1 1 aa
2 1 bb
3 2 cc
4 3 dd

------------------>
stepid step_sequence step_name next_sequence
1 1 aa cc
2 1 bb cc
3 2 cc dd


谢谢大家了，就是将表中step_sequence中下一步的作业提取出来显示。。。
谢谢大家

[xupeihuagudulei]

等牛人。

[liangCK]

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-12-02 11:19:59
-------------------------------------
 
--> 生成测试数据: @tb
DECLARE @tb TABLE (stepid INT,step_sequence INT,step_name VARCHAR(2))
INSERT INTO @tb
SELECT 1,1,'aa' UNION ALL
SELECT 2,1,'bb' UNION ALL
SELECT 3,2,'cc' UNION ALL
SELECT 4,3,'dd'

--SQL查询如下:

SELECT a.*,b.step_name as next_sequence 
FROM @tb AS A
    JOIN @tb AS B
ON A.step_sequence = B.step_sequence-1

[zhaoyh0530]

2楼高手

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-02 11:21:40
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([stepid] int,[step_sequence] int,[step_name] varchar(2))
insert [tb]
select 1,1,'aa' union all
select 2,1,'bb' union all
select 3,2,'cc' union all
select 4,3,'dd'
--------------开始查询--------------------------
select
  a.*,b.step_name
from 
 [tb] a,tb b
where 
  a.stepid=b.stepid-1
----------------结果----------------------------
/* stepid      step_sequence step_name step_name
----------- ------------- --------- ---------
1           1             aa        bb
2           1             bb        cc
3           2             cc        dd

(3 行受影响)
*/

[dawugui]

create table a( 
stepid int,   step_sequence  int,    step_name varchar(10))
insert into a values(1  ,        1  ,                'aa') 
insert into a values(2  ,        1  ,                'bb') 
insert into a values(3  ,        2   ,               'cc') 
insert into a values(4  ,        3   ,               'dd') 
go

select t.* , next_sequence 
  = (select top 1 step_name from a where step_sequence > t.step_sequence order by step_sequence)
from a t

drop table a 

/*
stepid      step_sequence step_name  next_sequence 
----------- ------------- ---------- ------------- 
1           1             aa         cc
2           1             bb         cc
3           2             cc         dd
4           3             dd         NULL

（所影响的行数为 4 行）
*/

[--小F--]

--关联错误
----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-02 11:21:40
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([stepid] int,[step_sequence] int,[step_name] varchar(2))
insert [tb]
select 1,1,'aa' union all
select 2,1,'bb' union all
select 3,2,'cc' union all
select 4,3,'dd'
--------------开始查询--------------------------
select
  a.*,b.step_name
from 
 [tb] a,tb b
where 
  a.step_sequence=b.step_sequence-1
----------------结果----------------------------
/*stepid      step_sequence step_name step_name
----------- ------------- --------- ---------
1           1             aa        cc
2           1             bb        cc
3           2             cc        dd

(3 行受影响)

*/

[dawugui]

如果step_sequence递增,用2,4楼,如果不是,用5楼.