如何使用纯SQL在表格的最小值和最大值之间找到差距 | 如何使用纯SQL在表格的最小值和最大值之间找到差距

本文介绍了如何使用纯SQL在表格的最小值和最大值之间找到差距的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

这是我的桌子

表1

ID序列
1 1
1 3
1 4
1 6
1 8
1 10

我想要以下输出
2
5
7
9

我已经试过了

Here is my table

Table1

ID Sequence
1 1
1 3
1 4
1 6
1 8
1 10

and i want this following output
2
5
7
9

I''ve tried this

SELECT Sequence FROM Table1 WHERE Sequence NOT IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10') and ID ='1'

推荐答案

WITH M AS (
    SELECT  Max(SEQUENCE) s
    FROM    table1
    WHERE   id = 1
    )
,numbers(n) AS
    (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM Numbers,m
    WHERE n + 1 <= s
)
SELECT  n
FROM    numbers
WHERE   n NOT IN (SELECT SEQUENCE FROM table1 WHERE id = 1)

CREATE FUNCTION [dbo].[GetMissingNumbers](@ID int)

RETURNS @MissingNr TABLE(MissingNumber int)
AS
begin

	DECLARE @minValue int
	DECLARE @maxValue int
	Declare @checkValue int
 
	select @minValue = min(Sequence) from Table1 where ID = @ID
	select @maxValue = max(Sequence) from Table1 where ID = @ID
	
	while @minValue < @maxValue
	begin
		set @minValue = @minValue+1
		select @checkValue = Count(Sequence) from Table1 where ID = @ID and Sequence = @minValue
		--print @checkValue
		if (ISNULL(@checkValue, 0) = 0) 
			insert into @MissingNr values (@minValue)
	end
	
	return
end

该函数的调用是:

call to the function is:

select *
from GetMissingNumbers(1)

select Sequence+1 from Table1 where Sequence+1 not in (select Sequence from Table1 
where id = 1) and id = 1 and Sequence not in (select max(Sequence) from Table1 where id = 1)

这篇关于如何使用纯SQL在表格的最小值和最大值之间找到差距的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！