这是表的结构如何的到下面的查询结果
------解决方案--------------------
select m.name,m.value,(select min(a.value) from table1 a where (a.value>m.value and a.name=m.name))value1 from table1 m order by name,value;
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-25 14:00:50
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([code] nvarchar(2),[value] int)
insert [huang]
select 'A',5 union all
select 'A',20 union all
select 'A',10 union all
select 'A',15 union all
select 'B',6 union all
select 'B',3 union all
select 'B',16 union all
select 'B',32
--------------生成数据--------------------------
IF OBJECT_ID('TEMPDB..#T','U') IS NOT NULL
DROP TABLE #T
select *,(SELECT COUNT(1) FROM HUANG B WHERE A.CODE=B.CODE AND A.value>=B.VALUE) AS ID INTO #T
from [huang] A
SELECT A.CODE,A.value,(SELECT value FROM #T B WHERE A.CODE=B.CODE AND A.ID+1=B.ID)AFTERVALUE
FROM #T A
ORDER BY CODE,ID
----------------结果----------------------------
/*
CODE value AFTERVALUE
---- ----------- -----------
A 5 10
A 10 15
A 15 20
A 20 NULL
B 3 6
B 6 16
B 16 32
B 32 NULL
*/