你想从表中找到Missing No。如果检查我的下面的查询，我已经创建了一个样本你。

   -   我为你创建了一个样本表 
 create   table  K_RT_Dailyentryretail（
 SNO  int  不  null   primary   key ，
分支 varchar （ 10 ）
）; 
  -   此处将样本数据插入表中，缺少编号 
 insert   into  K_RT_Dailyentryretail（SNO，Branch） values  
（ 1 ，'  ABC'），
（ 2 ，'  ABC'），
（ 3 ，'  ABC'），
（ 5 ，'  ABC'），
（ 6 ，'  ABC'），
（ 8 ，'  ABC'），
（  10 ，'  ABC'）
  -    select * from K_RT_Dailyentryretail  

  -   以下是仅从给定表中选择缺少的No的查询。 

选择 KRD.SNO +  1   as  MissingNos 
来自 K_RT_Dailyentryretail  as  KRD 
 left  外部  join  K_RT_Dailyentryretail  as  LKRD  on  KRD.SNO +  1  = LKRD.SNO 
其中 
 LKRD.SNO    null  

我看不出你怎么做，除非表格中的所有行拥有相同的分支。

  SELECT   DISTINCT 分支 FROM  K_RT_Dailyentryretail; 

但我认为很可能不是这种情况。



删除行，对吧？那么从何处获取信息？



一个选项可能是添加一个名为Status的列，其值为Active / Inactive而不是删除行，您可以更改状态。



另一种选择是将已删除的行存储在自己的表中。

 声明  @ id   int  
 set   @ id  =  0  
声明  @ maxid   int  

  -    set @id = @fromvalue  
选择  @ maxid  =（ select  count（*）dcno 来自 K_RT_Dailyentryretail  nolock  
）

 PRINT   @ maxid  
创建 表 #IDSeq 
（ 
 id  int  

）

 while  0< @maxid   -   无论您的最大值是什么 
开始 
插入 进入 #IDSeq  values （ @ id ）

 set   @ id  =  @ id  +  1  
 set   @ maxid  =  @ maxid   -   1  
  -    print @maxid  
 end  


创建  table  #MissingNumbers 
（
 id  int ，
 BRANCH  VARCHAR （MAX）
）

 INSERT   INTO  #MissingNumbers（id）
选择 
 s.id 
来自 
 #idseq s 
 left   join  K_RT_Dailyentryretail t  on  
 s.id = t.dcno 

其中 t。 dcno    null   order   by  s.id  asc  

 DECLARE   @ count   INT  =（ SELECT  MAX（id） FROM  #MissingNumbers）/ 50 
 DECLARE   @ value   INT  =  1  

 while  @ value< = @ count   -   无论你有什么最大值 
开始 
更新 #MissingNumbers  SET  BRANCH =（ SELECT   TOP   1  BRANCH  FROM  K_RT_Dailyentryretail  WHERE  dcno  BETWEEN （（ @ value-1 ）* 50）  AND （@ value * 50）） WHERE  id  IN （ SELECT  id  FROM  #MissingNumbers 其中 id  BETWEEN （（ @ value-1 ）* 50） AND （@ value * 50））
 set  @ value =  @ value  +  1  
  -    print @ maxid  
 end  

 SELECT  *  FROM  #MissingNumbers 
 drop   t能够 #IDSeq 
 drop   table  #MissingNumbers 

I want to find Missing Numbers in a table..Table Like this.

        Sno                Branch 
        1                   ABC
        2                   ABC
        3                   ABC
        5                   ABC   // 4th sno is missing
        6                   ABC
        8                   ABC   // 7th sno is missing
       10                   ABC   // 9th sno is missing 

I found the missing SNo using this Query

ALTER proc [dbo].[K_RT_DCNoMissing]--1,50


as
begin

declare @id int
set @id = 0
declare @maxid int

--set @id = @fromvalue
select @maxid = (select count(*) dcno  from K_RT_Dailyentryretail nolock
 )


create table #IDSeq
(
    id int

)

while 0<@maxid--whatever you max is
begin
    insert into #IDSeq values(@id)

   set @id = @id + 1
   set @maxid = @maxid - 1
  -- print @maxid
end

select
    s.id
    --,(select wd.branch from K_RT_Dailyentryretail d inner join K_RT_WarehouseDetails wd on d.branchdate=wd.sno where d.dcno is not null) as branch
from
    #idseq s
    left join K_RT_Dailyentryretail t on
        s.id = t.dcno

 where t.dcno is  null  order by s.id asc

 drop table #IDSeq

 end

I am getting out put like this..

MissingNo's
      4
      7
      9

Now I want to Display Sno with Branch Name like.

    MissingNo's           Branch
         4                   ABC
         7                   ABC
         9                   ABC

How can i get the branch name...

Hi,
Do you want to find the Missing No from a Table .If so check my below query i have created a sample for you.

-- I have created a Sample Table for you
create table K_RT_Dailyentryretail  (
    SNO int not null primary key,
    Branch  varchar(10)
);
-- here insert sample data to the table with missing no.
insert into K_RT_Dailyentryretail (SNO,Branch) values
    (1,'ABC'), 
	(2,'ABC'), 
	(3,'ABC'), 
	(5,'ABC'),
	 (6,'ABC'),
	  (8,'ABC'), 
	  (10,'ABC') 
--select * from K_RT_Dailyentryretail

-- Here is the query to select only the missing No from the given table.

select KRD.SNO + 1 as MissingNos
from K_RT_Dailyentryretail as KRD
  left outer join K_RT_Dailyentryretail as LKRD on KRD.SNO + 1 = LKRD.SNO
where 
LKRD.SNO is null

I can't see how you can do that, unless all rows in the table have the same Branch.

SELECT DISTINCT Branch FROM K_RT_Dailyentryretail;

But that is most likely not the case, I would presume.

The rows are deleted, right? So where to get the info?

One option could be to add a column called Status that has the values Active/Inactive and instead of deleting the row, you change the status.

Another option is to store deleted rows in its own table.

declare @id int
set @id = 0
declare @maxid int
 
--set @id = @fromvalue
select @maxid = (select count(*) dcno  from K_RT_Dailyentryretail nolock
 )
 
 PRINT @maxid
create table #IDSeq
(
    id int
 
)
 
while 0<@maxid--whatever you max is
begin
    insert into #IDSeq values(@id)
 
   set @id = @id + 1
   set @maxid = @maxid - 1
  -- print @maxid
end
 

 create table #MissingNumbers
(
    id int,
    BRANCH VARCHAR(MAX)
)

INSERT INTO #MissingNumbers(id) 
select
    s.id
from
    #idseq s
    left join K_RT_Dailyentryretail t on
        s.id = t.dcno
 
 where t.dcno is  null  order by s.id asc

 DECLARE @count INT = (SELECT MAX(id) FROM #MissingNumbers)/50
 DECLARE @value INT = 1
 
while @value<=@count--whatever you max is
begin
	UPDATE #MissingNumbers SET BRANCH = (SELECT TOP 1 BRANCH FROM K_RT_Dailyentryretail WHERE dcno BETWEEN ((@value-1)*50) AND (@value*50)) WHERE id IN (SELECT id FROM #MissingNumbers where id BETWEEN ((@value-1)*50) AND (@value*50))
    set @value= @value + 1
  -- print @maxid
end

  SELECT * FROM #MissingNumbers
  drop table #IDSeq
  drop table #MissingNumbers