Table1格式如下:
Col1
1
2
3
4
6
7
8
9
10
13
14
如上所示,col1具有值序列,但是由于某种原因,用户没有插入5、11等等。如何找出序列中的缺失值。这里的序列是1到14,缺失值是5,11。请帮助我。
发布于 2012-02-17 20:36:25
正如在其他答案中所说的,最好的选择是与真实的序列表进行连接。您可以使用递归CTE创建一个:
DECLARE @MaxNumber INT
SELECT @MaxNumber = MAX(Col1) FROM YourTable;
WITH CTE AS
(
SELECT 1 Col1
UNION ALL
SELECT Col1+1
FROM CTE
WHERE Col1+1 <= @MaxNumber
)
SELECT A.Col1
FROM CTE A
LEFT JOIN YourTable B
ON A.Col1 = B.Col1
WHERE B.Col1 IS NULL
OPTION(MAXRECURSION 0)
发布于 2012-02-17 20:32:24
这将适用于数字0- 2000对于较大的数字,您只需交叉连接原始结果集。
with temp as (
select distinct number
from master..spt_Values
where number between 0 and 2000
)
select * from
temp t
left join your_table y on y.col1 = t.number
where y.col1 is null
或者使用交叉连接
这将对数十亿人起作用,显然要慢一些。
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
select * from
l5 t
left join your_table y on y.col1 = t.n
where y.col1 is null
发布于 2012-02-17 20:34:33
这看起来很像是
SQL query to find Missing sequence numbers
有一个建议,这将是可行的:
SELECT l.id + 1 as start
FROM Table1 as l
LEFT JOIN Table1 as r on l.id + 1 = r.id
WHERE r.id IS NULL
否则,您可以在表上使用顺序表进行连接。从上面的问题中,您可以查看http://www.projectdmx.com/tsql/tblnumbers.aspx来了解如何生成合适的顺序表,连接将类似于
SELECT #sequence.value
FROM #sequence
LEFT JOIN Table1 ON #sequence.value = Table1.value
WHERE Table1.value IS NULL
https://stackoverflow.com/questions/9328145
复制相似问题