sql-server - 查找varchar变量中最后一个数值的序列

Question

我在一个表中有一列，它具有递增的值，例如:

AAA0000001
AAA0000002 

...等等

我想查明此列中存储的值是否按正确的顺序排列，或者两者之间是否有任何值丢失或被删除。

我怎样才能做到这一点？

Answer 1

假设模式总是:AAA[0-9][0-9][0-9][0-9][0-9][0-9][0-9] ，您可以使用 Tally Table 执行此操作。

示例数据:

CREATE TABLE Tbl(val VARCHAR(10))
INSERT INTO Tbl VALUES
('AAA0000001'), ('AAA0000002'), ('AAA0000004'), ('AAA0000011');

val
----------
AAA0000001
AAA0000002
AAA0000004
AAA0000011

SQL Fiddle

;WITH Cte AS(
    SELECT *,
        num = CAST(SUBSTRING(val, 4, LEN(val) - 3) AS INT)
    FROM Tbl
),
E1(N) AS(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
Tally(N) AS(
    SELECT TOP(SELECT MAX(num) FROM Cte)
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM E4
)
SELECT
    N, 
    val = 'AAA' + RIGHT('0000000' + CAST(N AS VARCHAR(7)), 7)
FROM Tally
WHERE NOT EXISTS(
    SELECT 1 FROM Cte WHERE num = N
)

结果

N                    val
-------------------- ----------
3                    AAA0000003
5                    AAA0000005
6                    AAA0000006
7                    AAA0000007
8                    AAA0000008
9                    AAA0000009
10                   AAA0000010

---

解释:

1. 第一个CTE , 命名为 Cte , 提取字符串的数字部分和 CAST把他们送到INT .
2. 继任者CTE s，来自 E1至 Tally(N)生成一个包含来自 1 的顺序值的表直到 MAX(num) - INT从第一个返回 CTE .
3. 决赛SELECT只检查不存在的 num从第一个CTE .
4. 'AAA' + RIGHT('0000000' + CAST(N AS VARCHAR(7)), 7)转换 N以便它遵循模式。