比方说，我有以下表中有三列：

Let's say I have the following table with three columns:

id | start_block | end_block
-----------------------------
01 | 00000000001 | 00000000005
02 | 00000000006 | 00000000011
03 | 00000000012 | 00000000018
04 | 00000000025 | 00000000031
05 | 00000000032 | 00000000043

每个行是一个开始块和结束块。如果数据是完美的，每个起始块将是比以前它的结束块中的一个以上。因此，对于行ID = = 02，起始块为6，而之前该行的结束块是5。

Each row was a "Start Block" and an "End Block". If the data was perfect, each start block would be one more than the end block before it. So, for row id == 02, the start block is 6 while the end block for the row before it 5.

我需要查询这些数据（它的几万行），并发现任何失踪行。根据我的采样数据，应该有在之间03和04具有的19起始块和24个结束块中的行。

I need to query this data (it's tens of thousands of row) and find any missing rows. According to my sample data, there should be a row in between 03 and 04 that has a start block of 19 and an end block of 24.

我试图建立在JSP中的一份报告调和这些数据，并找到失踪的行。丑陋的方式做，这将是拉动整个记录到一个数组，做这样的事情每一行：

I'm trying to build a report in JSP to reconcile this data and find the missing rows. The ugly way to do this would be to pull the whole recordset into an array and do something like on every row:

if ((arry(i,1) + 1) != (arry(i+1),1)( {
  print("Bad Row!\n");
}

不过，我真的很希望能直接查询记录并返回我需要什么。那可能吗？如果不是，可能有人点我创建一个存储过程，做什么，我需要？

But, I would really like to be able to query the recordset directly and return what I need. Is that possible? If not, could someone point me in the right direction of creating a stored proc that does what I need?

推荐答案

当然不会伤害到试一试

CREATE TABLE #t (startz INT, zend INT)
insert into #t (startz, zend) values (1,5)
insert into #t (startz, zend) values (6,11)
insert into #t (startz, zend) values (12,18)
insert into #t (startz, zend) values (25,31)
insert into #t (startz, zend) values (32,43)

select * from #t ta
LEFT OUTER JOIN #t tb ON tb.startz - 1 = ta.zend
WHERE tb.startz IS NULL

最后的结果是假阳性。但不易消除。

The last result is a false positive. But easy to eliminate.