查找序列中缺少的数字 [英] Find missing numbers in a sequence

问题描述

所以我正在尝试做一个程序（在VB2008 Windows应用程序表单上运行）如何使程序找到丢失的数字由用户连接到MSSQL数据库的给定数字范围。所以基本上用户在两个文本框中键入范围编号＃，比如让我们说1和10。但是在MSSQL数据库中，只有数字1和2.因此程序将读取数据库表的列，并将生成数字3,4,5,6,7,8,9和10的输出一个文本框。我的问题是可能的，我可以采用哪些方法或方法？



我想做的一个例子：

http：// www.get-digital-help.com/2009/06/13/identify-missing-values-in-a-column-using-excel-formula/ [ ^ ]

解决方案

 选择否来自 tbl 
 其中​​ Nos> = 1 和 a< = 10   -   过滤范围为nos  
 和    （  1 ， 2  ）  -   这里添加你有 
 
   -    o / p  
   -    3,4,5 ...  

快乐编码！

：）

这是一个样本方法。



2个变量@LowerLimit和@UpperLimit应该由用户提供。



< pre lang =sql> DECLARE @ LowerLimit INT
DECLARE @ UpperLimit INT
DECLARE @ MissingNumberList VARCHAR （MAX）

SET @ LowerLimit = 20
SET @ UpperLimit = 25 ;

CREATE TABLE #Sample
（
Number INT
）

INSERT INTO #Sample
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 16 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
< span class =code-keyword> SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION 所有
SELECT 1 UNION < span class =code-keyword> ALL
SELECT 2 UNION ALL
SELECT 10 ;




WITH CTE AS
（
SELECT @ LowerLimit AS N
UNION ALL
SELECT N + 1 AS N
FROM CTE WHERE N< @ UpperLimit
）

- SELECT N AS MissingNumbers FROM CTE
- LEFT JOIN #Sample S ON CTE.N = S.Number
- WHERE S.Number IS NULL

选择 @MissingNumberList = ISNULL（ @MissingNumberList ，' '）+ CAST（N AS VARCHAR ）+ ' ，' FROM CTE
LEFT JOIN #Sample S ON CTE.N = S.Number
WHERE S.Number IS NULL

IF @ MissingNumberList <> ' '
SET < span class =code-sdkkeyword> @MissingNumberList = SUBSTRING（ @ MissingNumberList ， 0 ，LEN（ @ MissingNumberList ） - 1）
SELECT @MissingNumberList AS MissingNumberList


DROP 表 #Sample

如果您有任何疑问，请与我们联系。

  create   table  test （number  int ）
 
  insert  进入 test 值（ 1 ）
 插入 进入 test  values （ 2 ）
  insert   into  test  values （ 3 ）
 插入 进入 test 值（ 5 ）
  insert  进入 test  values （ 6 ）
  insert   into  test  values （ 10 ）
 插入 进入 test 值（ 11 ）
 
 声明  @ num   int  
  set  @ num = 1 
  while   @ num < =（ select  max（numbers）来自测试）
 开始 
 如果（  @ num  =（选择数字来自 test 其中 numbers = @ num））
 开始 
  print   @ num  
  end  
  else  
 开始 
 选择 <水疗中心n class =code-sdkkeyword> @ num  '  Num' 
  end  
  set  @ num = @ num +1 
 
 < span class =code-keyword> end  

Hi,

So i''m trying to do a program(run on VB2008 Windows Application Forms) on how to make the program find missing numbers from a given range of numbers by the users where it is connected to an MSSQL Database. So basically the user type in the range number # from two textboxes like let''s say "1" and "10". But on the MSSQL database, there are only numbers 1 and 2. So the program will read the database table''s column and will generate an output of numbers 3, 4, 5, 6, 7, 8, 9, and 10 on a textbox. My question is it possible and what methods or ways can i do that?

An example of what i''m trying to do:
http://www.get-digital-help.com/2009/06/13/identify-missing-values-in-a-column-using-excel-formula/[^]

解决方案

select Nos from tbl
where Nos >=1 and a<=10  --filter range of nos
and Nos not in (1,2)     --here add nos you have

--o/p
--3,4,5...10

Happy Coding!
:)

Here is a sample approach.

The 2 variables @LowerLimit and @UpperLimit should be supplied by users.

DECLARE @LowerLimit INT
DECLARE @UpperLimit INT
DECLARE @MissingNumberList VARCHAR(MAX)

SET @LowerLimit = 20
SET @UpperLimit = 25;

CREATE TABLE #Sample
(
    Number INT
)

INSERT INTO #Sample
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 16 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 10;




WITH CTE AS
(
    SELECT @LowerLimit AS N
    UNION ALL
    SELECT N + 1 AS N
    FROM CTE WHERE N < @UpperLimit
)

--SELECT N AS MissingNumbers FROM CTE
--LEFT JOIN #Sample S ON CTE.N = S.Number
--WHERE S.Number IS NULL

SELECT @MissingNumberList = ISNULL(@MissingNumberList,'') + CAST(N AS VARCHAR) + ', ' FROM CTE
LEFT JOIN #Sample S ON CTE.N = S.Number
WHERE S.Number IS NULL

IF @MissingNumberList <> ''
SET @MissingNumberList = SUBSTRING(@MissingNumberList, 0, LEN(@MissingNumberList)-1)
SELECT @MissingNumberList AS MissingNumberList


DROP TABLE #Sample

Let me know if you have any questions.

create table test(numbers int)
 
insert into test values(1)
insert into test values(2)
insert into test values(3)
insert into test values(5)
insert into test values(6)
insert into test values(10)
insert into test values(11)

declare @num int
set @num=1
while @num <=(select max (numbers)from test)
begin
	if(@num = (select numbers from test where numbers=@num))
		begin
			print @num	
		end
	else
		begin 
			select @num 'Num'
		end
	set @num=@num +1
	
end

这篇关于查找序列中缺少的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！