go CREATE FUNCTION [dbo].[m_split] ( @c VARCHAR(2000) , @split VARCHAR(2) ) RETURNS @t TABLE ( col VARCHAR(200) ) AS BEGIN WHILE ( CHARINDEX(@split, @c) <> 0 ) BEGIN INSERT @t ( col ) VALUES ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) - 1) ) SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), '') END INSERT @t ( col ) VALUES ( @c ) RETURN END goDECLARE @sql VARCHAR(30) SET @sql = '2,3,4,5,6,4,3,2,1,2,3,4,5,6,7,5,3,2,4,5,6,8' CREATE TABLE #t ( id INT IDENTITY, v INT ) INSERT INTO #T SELECT * FROM dbo.m_split(@sql, ',') ; WITH maco AS ( SELECT a.id , a.v - b.v AS c , a.v FROM #t a LEFT JOIN #t b ON a.id = b.id - 1 ) SELECT id AS 位置 , v AS 数值 FROM maco WHERE c IN ( SELECT TOP 2 c FROM maco ORDER BY c DESC )DROP TABLE #t/* 位置 数值 ----------- ----------- 5 6 15 7 */
go
CREATE FUNCTION [dbo].[m_split]
(
@c VARCHAR(2000) ,
@split VARCHAR(2)
)
RETURNS @t TABLE ( col VARCHAR(200) )
AS
BEGIN
WHILE ( CHARINDEX(@split, @c) <> 0 )
BEGIN
INSERT @t
( col
)
VALUES ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) - 1)
)
SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), '')
END
INSERT @t
( col )
VALUES ( @c )
RETURN
END
goDECLARE @sql VARCHAR(30)
SET @sql = '2,3,4,5,6,4,3,2,1,2,3,4,5,6,7,5,3,2,4,5,6,8'
CREATE TABLE #t ( id INT IDENTITY, v INT )
INSERT INTO #T
SELECT *
FROM dbo.m_split(@sql, ',') ;
WITH maco
AS ( SELECT a.id ,
a.v - b.v AS c ,
a.v
FROM #t a
LEFT JOIN #t b ON a.id = b.id - 1
)
SELECT id AS 位置 ,
v AS 数值
FROM maco
WHERE c IN ( SELECT TOP 2
c
FROM maco
ORDER BY c DESC )DROP TABLE #t/*
位置 数值
----------- -----------
5 6
15 7
*/