CodePudding user response:
The test data-DECLARE @ TEMP TABLE (kin varchar (50), loc varchar (50))
Insert into @ TEMP
Select the 'A', 'ZZ - 3-63' UNION ALL
Select the 'A', 'ZZ - 3-64' UNION ALL
Select the 'A', 'ZZ - 3-65' UNION ALL
Select the 'A', 'ZZ - 3-67' UNION ALL
Select the 'A', 'ZZ - 3-68' UNION ALL
Select the 'A', 'ZZ - 3-71
- remove "-", as the serial number
; With cte as (
The SELECT A.k in a. oc,
The SUBSTRING (REPLACE (a. oc, '-', '), 3, LEN (loc) - 3) AS seq
The FROM @ TEMP A)
- find out a serial number on each of the loc, subtraction, if greater than 1 is the number
The SELECT A.k in a. oc,
PreSqr=isNull ((SELECT TOP 1 seq FROM cte B WHERE B.s eqThe FROM CTE A
Where A.s eq - CAST (isNull ((SELECT TOP 1 seq FROM cte B where B.s eq
CodePudding user response:
The CREATE TABLE # A
(
KIN VARCHAR (20),
LOC VARCHAR (20)
)
# INSERT INTO A VALUES (' A ', 'ZZ - 3-61')
# INSERT INTO A VALUES (' A ', 'ZZ - 3-62')
# INSERT INTO A VALUES (' A ', 'ZZ - 3-63')
# INSERT INTO A VALUES (' A ', 'ZZ - 3-65')
# INSERT INTO A VALUES (' A ', 'ZZ - 3-66')
# INSERT INTO A VALUES (' A ', 'ZZ - 3-67')
# INSERT INTO A VALUES (' A ', 'ZZ - 3-69')
The SELECT AA. KIN, AA. LOC FROM (
SELECT A. *, CONVERT (INT, REPLACE (RIGHT (a. OC, 4), '-', ')) LOC1 FROM # aa
) AA WHERE AA. LOC1 - (SELECT TOP 1 BB. LOC1 FROM (
SELECT A. *, CONVERT (INT, REPLACE (RIGHT (a. OC, 4), '-', ')) LOC1 FROM # aa
BB WHERE BB. LOC1 & lt; AA. LOC1 ORDER BY BB. LOC1 DESC)!=1 ORDER BY AA. LOC1 DESC
DROP TABLE # A
