PROSAGA码农传奇-sybase-SQL Anywhere：查找与另一行相比为+ -2的行

SQL Anywhere：查找与另一行相比为+ -2的行

作者: 记忆只剩空城
发布时间: 2024-12-09 01:11:16 (1月前)
转自：

<div class =“post-text”itemprop =“text”>
  <P>
    一种快速而肮脏的通用方法。
  </p>
  <P>
    更换
     <code>
      @User
    </code>
     与任何你想删除的人。
  </p>
   <pre>
    <code>
      DECLARE @table TABLE ( 
    ID Int
    ,[User] VARCHAR(2) 
    ,Form VARCHAR(3)
    ,Depth INT 
)

DECLARE @User VARCHAR(2) = 'A'

INSERT INTO @table (ID , [User], Form, Depth)
VALUES 
    (1 , 'A' , 'ABC' , 2001),
    (1 , 'A' , 'XYZ' , 1001),
    (1 , 'B' , 'XYZ' , 1003),
    (1 , 'B' , 'DEF' , 3001),
    (1 , 'C' , 'XYZ' , 1000)

SELECT t1.ID, t1.[User], t1.Form, t1.Depth , ROW_NUMBER() OVER(ORDER BY t1.ID, t1.[User], t1.Form, t1.Depth) AS [row_number] 
INTO #temp 
FROM @table as t1 
INNER JOIN ( 
    SELECT t.ID, t.Form, COUNT('8') as [count] 
    FROM @table as t
    GROUP BY ID, Form 
    HAVING COUNT('8') > 1 
) as duplicates
ON duplicates.ID = t1.ID 
AND duplicates. Form = t1.Form 
ORDER BY ID, User, Form, Depth

-- SELECT * FROM #temp

SELECT [row_number] - 2 as value 
INTO #range 
FROM #temp as t
WHERE t.[User] = @User

--SELECT * FROM #range

INSERT INTO #range 
SELECT [row_number] - 1 
FROM #temp as t 
WHERE t.[User] = @User

INSERT INTO #range 
SELECT [row_number] + 1 
FROM #temp as t
WHERE t.[User] = @User

INSERT INTO #range 
SELECT [row_number] + 2
FROM #temp as t 
WHERE t.[User] = @User

SELECT * FROM #temp 
WHERE [row_number] IN (SELECT value FROM #range)

DROP TABLE #temp 
DROP TABLE #range

</code>
  </pre>
</DIV>