如果行是连续50个数字的系列，如何删除行 [英] how to delete rows if they are in consecutive series of 50 numbers

问题描述

我必须根据条件检查行是否按照50个数字的顺序排列，最后一个数字是50或00 
 

 示例 
 
 01-50 
 51-100 
 101-150 
 151-200 
 201-250 
 
 
如果我发现连续50系列介于两者之间这些数字。我必须删除这些数字。 
 
 
如果找到的数字从 201-250 开始，那么我必须删除那些系列
如果我发现 211-260 我不应该删除这些行。

我需要一个sql server查询或C＃gridview。 < br $>




这是我的代码。记录丢失的记录。

;  WITH 缺失（missdcno， maxid）
  AS  
（
  SELECT   1   AS  missdcno，（
 选择 max（cast （R.dcno  as   int ））来自 K_RT_WarehouseDetails D 
  inner   join  K_RT_DailyentryRetail R  on  D.sno = R.branchdate 其中 len（DCNO）！> 5）
  UNION   ALL  
  SELECT  missdcno +  1 ，maxid  FROM 缺少
  WHERE  missdcno< maxid 
）
  SELECT  missdcno 
  FROM 缺少
  LEFT   OUTER   JOIN  K_RT_DailyentryRetail tt  on  tt.dcno = Missing.missdcno 
  WHERE  tt.dcno 是  NULL  和 missdcno 不    1  和  100  
  OPTION （MAXRECURSION  0 ）; 

这里输出作为子结果。从这个结果我必须写主查询。我我没有得到任何想法。

解决方案

wing SQL允许我在我的数据库中找到连续的发票：

 选择 inv.InvoiceId  as  StartInvoiceId，inv.InvoiceId +  49   as  EndInvoiceId 
 来自 accounting.invoice  as  inv 
 其中（inv.InvoiceId  -   1 ）％ 50  =  0  
 和  50  =（选择 count（*）来自 accounting.invoice 
 其中​​ invoiceId   inv.InvoiceId 和 inv.InvoiceId +  49 ）
  ord呃  by  inv.InvoiceId 

您应该能够将其转换为您的需求。

I have to check whether the rows are in sequence of 50 numbers based on condition the ending number last digit is either 50 or 00

Example

   01-50   
   51-100
   101-150
   151-200
   201-250
   

If I found the consecutive series of 50 is in between these numbers . I have to delete those numbers.


supose if found numbers starting from 201-250 then i have to delete those series
 if i found 211-260  i should not delete these rows.

I need a sql server query or C# gridview.


Here is my code. to fectch the missing records.

;WITH Missing (missdcno, maxid)
AS
(
 SELECT 1 AS missdcno, (
select max(cast(R.dcno as int))  from   K_RT_WarehouseDetails D
inner join K_RT_DailyentryRetail R on D.sno=R.branchdate where  len(DCNO) !>5   )
 UNION ALL
 SELECT missdcno + 1, maxid FROM Missing
 WHERE missdcno < maxid
)
SELECT missdcno
FROM Missing
LEFT OUTER JOIN K_RT_DailyentryRetail tt on tt.dcno = Missing.missdcno
WHERE tt.dcno is NULL and missdcno not between 1 and 100
OPTION (MAXRECURSION 0);

Here output comes as a sub result .From this result i have to write Main query.I am not getting any Idea.

解决方案

The following SQL allows me to find consecutive invoices on my database:

select inv.InvoiceId as StartInvoiceId, inv.InvoiceId + 49 as EndInvoiceId
from accounting.invoice as inv
where (inv.InvoiceId - 1) % 50 = 0
and 50 = (select count(*) from accounting.invoice
	where invoiceId between inv.InvoiceId and inv.InvoiceId + 49)
order by inv.InvoiceId

You should be able to convert it to your needs.

这篇关于如果行是连续50个数字的系列，如何删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！