如何在IDENTITY列中获取blanck可用值 [英] How to get the blanck available value in IDENTITY column
本文介绍了如何在IDENTITY列中获取blanck可用值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
喜欢
1
2
3
4 br />
15
16
17
i want检索6,7,8,9,10,11,12,13,14
我怎么能找到有价值的东西
解决方案
试试这个:
DECLARE @ tmp 表(ID INT )
INSERT INTO @ tmp (ID)
VALUES ( 1 )
INSERT INTO @ tmp (ID)
VALUES ( 2 )
INSERT INTO @ tmp (ID)
VALUES (< span class =code-digit> 3 )
INSERT INTO < span class =code-sdkkeyword> @ tmp (ID)
VALUES ( 4 )
INSERT INTO @ tmp (ID)
VALUES ( 15 )
INSERT INTO @ tmp (ID)
VALUES ( 16 )
INSERT INTO @ tmp (ID)
VALUES ( 17 )
DECLARE @ mId INT
SELECT @ mId = MAX(ID )
FROM @ tmp
; WITH NewIds AS
(
SELECT 1 AS nID
UNION ALL
SELECT nID + 1
FROM NewIds
WHERE nID< @mId
)
SELECT ni.nID
FROM NewIds AS ni
WHERE ni.nID NOT IN ( SELECT ID AS nID FROM @ tmp )
nID 5 6 7 8 9 10 11 12 13 14
顺便说一句......糟糕的桌子设计!请改变你的表格,但首先阅读它: IDENITY [ ^ ]
示例表格设计:
CREATE TABLE MyTable(ID INT INDENTITY( 1 , 1 ),Column2 VARCHAR ( 30 ),Column3 INT )
[/ EDIT]
我认为你需要一个数字表。我通过搜索找到了这个:数字表 [ ^ ]
like
1
2
3
4
15
16
17
i want to retrieve 6,7,8,9,10,11,12,13,14
how i can find with value is missing
解决方案
Try this:
DECLARE @tmp TABLE (ID INT) INSERT INTO @tmp (ID) VALUES(1) INSERT INTO @tmp (ID) VALUES(2) INSERT INTO @tmp (ID) VALUES(3) INSERT INTO @tmp (ID) VALUES(4) INSERT INTO @tmp (ID) VALUES(15) INSERT INTO @tmp (ID) VALUES(16) INSERT INTO @tmp (ID) VALUES(17) DECLARE @mId INT SELECT @mId = MAX(ID) FROM @tmp ;WITH NewIds AS ( SELECT 1 AS nID UNION ALL SELECT nID + 1 FROM NewIds WHERE nID<@mId ) SELECT ni.nID FROM NewIds AS ni WHERE ni.nID NOT IN (SELECT ID AS nID FROM @tmp)
In above code CTE[^] were used.
result:
nID 5 6 7 8 9 10 11 12 13 14
[EDIT]
By The Way... Bad table design! Please, alter your table, but first read it: IDENITY[^]
Example table design:
CREATE TABLE MyTable (ID INT INDENTITY(1,1), Column2 VARCHAR(30), Column3 INT)
[/EDIT]
I think you need a numbers table. I found this with a search: numbers table[^]
这篇关于如何在IDENTITY列中获取blanck可用值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文