none
一个字段是自增长,int类型,怎样查询出这个字段中缺失的内容 RRS feed

答案

  • 参考这里

    http://sql-server-helper.com/tips/determine-missing-identity-values.aspx

    解答摘抄如下

    方法一:

    -- Step #3 (Option #1): Identify Missing IDENTITY Values
    DECLARE @ID INT
    DECLARE @MaxID INT
    DECLARE @MissingCarTypeIDs TABLE ( [ID] INT )

    SELECT @MaxID = [ID] FROM #CarType

    SET @ID = 1
    WHILE @ID <= @MaxID
    BEGIN
        IF NOT EXISTS (SELECT 'X' FROM #CarType
                       WHERE [ID] = @ID)
            INSERT INTO @MissingCarTypeIDs ( [ID] )
            VALUES ( @ID )

        SET @ID = @ID + 1
    END

    SELECT * FROM @MissingCarTypeIDs

    方法二:

    -- Step #3 (Option #2): Identify Missing IDENTITY Values
    DECLARE @IntegerTable TABLE ( [ID] INT )
    DECLARE @ID INT
    DECLARE @MaxID INT

    SELECT @MaxID = [ID] FROM #CarType

    SET @ID = 1
    WHILE @ID <= @MaxID
    BEGIN
        INSERT INTO @IntegerTable ( [ID] )
        VALUES ( @ID )

        SET @ID = @ID + 1
    END

    SELECT A.*
    FROM @IntegerTable A LEFT OUTER JOIN #CarType B
      ON A.[ID] = B.[ID]
    WHERE B.[ID] IS NULL

    方法三:

    -- Step #3 (Option #3): Identify Missing IDENTITY Values
    DECLARE @MaxID INT

    SELECT @MaxID = [ID] FROM #CarType

    SELECT A.*
    FROM [dbo].[ufn_GenerateIntegers] ( @MaxID ) A LEFT OUTER JOIN #CarType B
      ON A.[IntValue] = B.[ID]
    WHERE B.[ID] IS NULL


    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    • 已标记为答案 kZhj 2016年6月20日 1:27
    2016年6月18日 3:51