vfp9.0sp2环境下一个表tb中有一字符型字段xh:
xh
32
34
35
36
38
39
41
42
43
47
48
想找到不连续的xh:33、37、40、44、45、46,怎么写命令?
------解决方案--------------------
1、生成1-N的数列,与工作表连接;
2、
SELECT a.*,b.xh FROM r:temptty a LEFT JOIN r:temptty b ON a.id=b.id+1
循环判断两个XH是否连续
------解决方案--------------------
- SQL code
Create Cursor tb (xh i) Insert Into tb Values (32) Insert Into tb Values (34) Insert Into tb Values (35) Insert Into tb Values (36) Insert Into tb Values (38) Insert Into tb Values (39) Insert Into tb Values (41) Insert Into tb Values (42) Insert Into tb Values (43) Insert Into tb Values (47) Insert Into tb Values (48) Create Cursor t1 (xh i) For m.lnI=32 To 48 Insert Into t1 Values (m.lnI) Endfor Select t1.xh From t1 Where xh Not In (Select xh From tb) * 或 Select t1.xh From t1 Left Join tb On t1.xh=tb.xh Where tb.xh Is Null
------解决方案--------------------
sele xh from tb where xh not in (sele recno()+31 from tb)
???