select a.a1,b.a2 from (select max(id) a1 from table where id<'15') a, (select min(id) a2 from table where id>'15') b
CREATE TABLE TBTEST(ID INT, name VARCHAR(10)) INSERT TBTEST SELECT 15 , 'aa ' UNION SELECT 2 , ' bb 'UNION SELECT 4 , ' cc '--SELECT * FROM TBTESTSELECT IDD =IDENTITY(INT,1,1),* INTO #TB FROM TBTESTSELECT * FROM #TBIF (SELECT IDD FROM #TB WHERE ID=2)-1=0 BEGIN SELECT DISTINCT CASE WHEN (SELECT IDD FROM #TB WHERE ID=2)-1=0 THEN NULL ELSE ID END AS ID FROM #TB UNION ALLSELECT ID FROM #TB WHERE IDD=(SELECT IDD FROM #TB WHERE ID=2)+1 END ELSE BEGIN SELECT ID FROM #TB WHERE IDD=(SELECT IDD FROM #TB WHERE ID=2)-1UNION ALLSELECT ID FROM #TB WHERE IDD=(SELECT IDD FROM #TB WHERE ID=2)+1 ENDID ----------- NULL 4(所影响的行数为 2 行)
4楼的。假如表里面本身就存在自动增长列。。那么你的那种方法会报错的。。 所以要把SELECT IDD =(row_number() over(order by NSPN_ID desc)),* INTO #TB FROM TBTEST 就可以了
(select max(id) a1 from table where id<'15') a,
(select min(id) a2 from table where id>'15') b
INSERT TBTEST
SELECT 15 , 'aa ' UNION
SELECT 2 , ' bb 'UNION
SELECT 4 , ' cc '--SELECT * FROM TBTESTSELECT IDD =IDENTITY(INT,1,1),* INTO #TB FROM TBTESTSELECT * FROM #TBIF (SELECT IDD FROM #TB WHERE ID=2)-1=0 BEGIN
SELECT DISTINCT CASE WHEN (SELECT IDD FROM #TB WHERE ID=2)-1=0 THEN NULL ELSE ID END AS ID
FROM #TB UNION ALLSELECT ID FROM #TB
WHERE IDD=(SELECT IDD FROM #TB WHERE ID=2)+1
END ELSE BEGIN
SELECT ID FROM #TB WHERE IDD=(SELECT IDD FROM #TB WHERE ID=2)-1UNION ALLSELECT ID FROM #TB
WHERE IDD=(SELECT IDD FROM #TB WHERE ID=2)+1
ENDID
-----------
NULL
4(所影响的行数为 2 行)
所以要把SELECT IDD =(row_number() over(order by NSPN_ID desc)),* INTO #TB FROM TBTEST 就可以了