--> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([ID] [int],[Sort] [int]) INSERT INTO [tb] SELECT '3','2' UNION ALL SELECT '6','1' UNION ALL SELECT '8','3';WITH t AS ( SELECT rn=ROW_NUMBER()OVER(ORDER BY sort,ID),* FROM tb ) SELECT TOP 1 ID,Sort FROM t WHERE rn>( SELECT rn FROM t WHERE id = 6 ) ORDER BY Sort /* ID Sort ----------- ----------- 3 2(1 行受影响) */ TRUNCATE TABLE tb INSERT INTO [tb] SELECT '3','2' UNION ALL SELECT '6','1' UNION ALL SELECT '8','2';WITH t AS ( SELECT rn=ROW_NUMBER()OVER(ORDER BY sort,ID),* FROM tb ) SELECT TOP 1 ID,Sort FROM t WHERE rn>( SELECT rn FROM t WHERE id = 6 ) ORDER BY Sort /* ID Sort ----------- ----------- 3 2(1 行受影响) */ TRUNCATE TABLE tb INSERT INTO [tb] SELECT '3','0' UNION ALL SELECT '6','0' UNION ALL SELECT '8','0';WITH t AS ( SELECT rn=ROW_NUMBER()OVER(ORDER BY sort,ID),* FROM tb ) SELECT TOP 1 ID,Sort FROM t WHERE rn>( SELECT rn FROM t WHERE id = 6 ) ORDER BY Sort /* ID Sort ----------- ----------- 8 0(1 行受影响) */
WITH 这个好像是SQL2005用的吧,我用的2000不支持哦!
try as follow,maybe you can get the ID ...select top 1 t.ID from ( select distinct top 1000 ID from tb order by ID asc ) t where t.ID <>6
实在没看明白你想要什么?既然你说Tony 的是正确的 帮你改写一下吧:;WITH t AS ( SELECT rn = (SELECT COUNT(*) FROM tb WHERE id <= a.id ),* FROM tb a ) SELECT TOP 1 ID,Sort FROM t WHERE rn>( SELECT rn FROM t WHERE id = 6 ) ORDER BY Sort 2000 支不支持with as 我就不清楚了,如果不支持自己改一下吧。 如果可以使用临时表的话还可以用下面的语法新增行号列。 SELECT IDENTITY(INT) rn ,* INTO #t FROM tb ORDER BY id
select top 1 id,sort from tb where (sort=(select sort from tb where id=6) and id>6) or sort>(select sort from tb where id=6) order by sort,id
csdnadmin 的现在还没试,上面的自增值也不行,我已经设了主键自增值了。还有就是那个 SELECT COUNT(*) FROM tb WHERE id <= a.id 为什么你们就看不明白呢? ROW_NUMBER()OVER(ORDER BY sort,ID),* FROM tb ORDER BY 两个字段了,sort为主,ID为辅 你就用个id<=a.id 结果也不一样啊,id大小是没法区分的!
if exists (select min(sort) from tb where id>6) select * from tb where sort=(select min(sort) from tb where id>6)
select * from tb where sort=(select min(sort) from tb where sort>(select sort from tb where id=6))
--> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([ID] [int],[Sort] [int]) INSERT INTO [tb] SELECT '3','2' UNION ALL SELECT '6','1' UNION ALL SELECT '8','3'SELECT rn=IDENTITY(INT),* INTO #t FROM tb ORDER BY sort,ID SELECT TOP 1 ID,Sort FROM #t WHERE rn>( SELECT rn FROM #t WHERE id = 6 ) ORDER BY rn DROP TABLE #t GO /* ID Sort ----------- ----------- 3 2(1 行受影响) */ TRUNCATE TABLE tb INSERT INTO [tb] SELECT '3','2' UNION ALL SELECT '6','1' UNION ALL SELECT '8','2'SELECT rn=IDENTITY(INT),* INTO #t FROM tb ORDER BY sort,ID SELECT TOP 1 ID,Sort FROM #t WHERE rn>( SELECT rn FROM #t WHERE id = 6 ) ORDER BY rn DROP TABLE #t GO /* ID Sort ----------- ----------- 3 2(1 行受影响) */ TRUNCATE TABLE tb INSERT INTO [tb] SELECT '3','0' UNION ALL SELECT '6','0' UNION ALL SELECT '8','0' SELECT rn=IDENTITY(INT),* INTO #t FROM tb ORDER BY sort,ID SELECT TOP 1 ID,Sort FROM #t WHERE rn>( SELECT rn FROM #t WHERE id = 6 ) ORDER BY rn DROP TABLE #t /* ID Sort ----------- ----------- 8 0(1 行受影响) */2000用临时表处理
楼上说用游标正解,因为对SQLSERVER数据库不了解,下面存储过程可能语法有问题,但思路应该正确CREATE PROCEDURE sp_GetNextID AS DECLARE @lastID int, @currentID int,@nextID intDECLARE table_cur CURSOR FOR SELECT ID,Sort FROM mytable order by ID,SortOPEN table_cur FETCH NEXT FROM table_cur INTO @currentID IF @lastID = 6 begin @nextID = @currentID; end @lastID = @currentID; FETCH NEXT FROM table_cur INTO @TableName END DEALLOCATE table_cur
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[Sort] [int])
INSERT INTO [tb]
SELECT '3','2' UNION ALL
SELECT '6','1' UNION ALL
SELECT '8','3';WITH t AS
(
SELECT rn=ROW_NUMBER()OVER(ORDER BY sort,ID),*
FROM tb
)
SELECT TOP 1 ID,Sort
FROM t
WHERE rn>(
SELECT rn
FROM t
WHERE id = 6
)
ORDER BY Sort
/*
ID Sort
----------- -----------
3 2(1 行受影响)
*/
TRUNCATE TABLE tb
INSERT INTO [tb]
SELECT '3','2' UNION ALL
SELECT '6','1' UNION ALL
SELECT '8','2';WITH t AS
(
SELECT rn=ROW_NUMBER()OVER(ORDER BY sort,ID),*
FROM tb
)
SELECT TOP 1 ID,Sort
FROM t
WHERE rn>(
SELECT rn
FROM t
WHERE id = 6
)
ORDER BY Sort
/*
ID Sort
----------- -----------
3 2(1 行受影响)
*/
TRUNCATE TABLE tb
INSERT INTO [tb]
SELECT '3','0' UNION ALL
SELECT '6','0' UNION ALL
SELECT '8','0';WITH t AS
(
SELECT rn=ROW_NUMBER()OVER(ORDER BY sort,ID),*
FROM tb
)
SELECT TOP 1 ID,Sort
FROM t
WHERE rn>(
SELECT rn
FROM t
WHERE id = 6
)
ORDER BY Sort
/*
ID Sort
----------- -----------
8 0(1 行受影响)
*/
select distinct top 1000 ID from tb order by ID asc ) t
where t.ID <>6
(
SELECT rn = (SELECT COUNT(*) FROM tb WHERE id <= a.id ),*
FROM tb a
)
SELECT TOP 1 ID,Sort
FROM t
WHERE rn>(
SELECT rn
FROM t
WHERE id = 6
)
ORDER BY Sort 2000 支不支持with as 我就不清楚了,如果不支持自己改一下吧。
如果可以使用临时表的话还可以用下面的语法新增行号列。
SELECT IDENTITY(INT) rn ,* INTO #t FROM tb ORDER BY id
select top 1 id,sort from tb where (sort=(select sort from tb where id=6) and id>6) or sort>(select sort from tb where id=6) order by sort,id
csdnadmin 的现在还没试,上面的自增值也不行,我已经设了主键自增值了。还有就是那个
SELECT COUNT(*) FROM tb WHERE id <= a.id 为什么你们就看不明白呢?
ROW_NUMBER()OVER(ORDER BY sort,ID),*
FROM tb
ORDER BY 两个字段了,sort为主,ID为辅 你就用个id<=a.id 结果也不一样啊,id大小是没法区分的!
select * from tb where sort=(select min(sort) from tb where id>6)
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[Sort] [int])
INSERT INTO [tb]
SELECT '3','2' UNION ALL
SELECT '6','1' UNION ALL
SELECT '8','3'SELECT rn=IDENTITY(INT),* INTO #t
FROM tb
ORDER BY sort,ID
SELECT TOP 1 ID,Sort
FROM #t
WHERE rn>(
SELECT rn
FROM #t
WHERE id = 6
)
ORDER BY rn
DROP TABLE #t
GO
/*
ID Sort
----------- -----------
3 2(1 行受影响)
*/
TRUNCATE TABLE tb
INSERT INTO [tb]
SELECT '3','2' UNION ALL
SELECT '6','1' UNION ALL
SELECT '8','2'SELECT rn=IDENTITY(INT),* INTO #t
FROM tb
ORDER BY sort,ID
SELECT TOP 1 ID,Sort
FROM #t
WHERE rn>(
SELECT rn
FROM #t
WHERE id = 6
)
ORDER BY rn
DROP TABLE #t
GO
/*
ID Sort
----------- -----------
3 2(1 行受影响)
*/
TRUNCATE TABLE tb
INSERT INTO [tb]
SELECT '3','0' UNION ALL
SELECT '6','0' UNION ALL
SELECT '8','0'
SELECT rn=IDENTITY(INT),* INTO #t
FROM tb
ORDER BY sort,ID
SELECT TOP 1 ID,Sort
FROM #t
WHERE rn>(
SELECT rn
FROM #t
WHERE id = 6
)
ORDER BY rn
DROP TABLE #t
/*
ID Sort
----------- -----------
8 0(1 行受影响)
*/2000用临时表处理
AS
DECLARE @lastID int, @currentID int,@nextID intDECLARE table_cur CURSOR FOR
SELECT ID,Sort FROM mytable order by ID,SortOPEN table_cur
FETCH NEXT FROM table_cur INTO @currentID
IF @lastID = 6
begin
@nextID = @currentID;
end
@lastID = @currentID;
FETCH NEXT FROM table_cur INTO @TableName
END
DEALLOCATE table_cur