一个不知是否有解的SQL解决方法

一个不知是否有解的SQL
tb_a
a(int) b(int) c(int)
1 100 1
2 99 1
3 98 2
4 96 3
5 93 1
6 93 3
7 90 2
8 88 1
9 87 9
10 87 7
11 80 10

解释：tb_a中a是计算顺序，当前记录中的b-c的结果应该等于下一条记录的b；然后b-c的结果接着等于下一条记录的b，如此循环。
问题：能否使用sql（SQL是非过程语言可能不行）或者存储过程或者其他方式查找出不符合此规律的记录，结果应为：
a(int) b(int) c(int)
6 93 3
10 87 7

------解决方案--------------------

SQL code

declare @test table(aa int,bb int,cc int)
insert into @test
select 1,100,1 union all
select 2,99,1 union all
select 3,98,2 union all
select 4,96,3 union all
select 5,93,1 union all
select 5,93,3 union all
select 7,90,2 union all
select 8,88,1 union all
select 9,87,9 union all
select 9,87,7 union all
select 11,80,10 

select row_number() over (order by aa,bb) as RowNum,aa,bb,cc 
into #TempTable
from @test

SELECT b.*  from #TempTable a left join #TempTable b on a.RowNum=b.RowNum-1
where a.bb-a.cc <>b.bb 

drop table #TempTable

------解决方案--------------------
如果连号

SQL code

db2> select * from tb_a;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |  100 |    1 |
|    2 |   99 |    1 |
|    3 |   98 |    2 |
|    4 |   96 |    3 |
|    5 |   93 |    1 |
|    6 |   93 |    3 |
|    7 |   90 |    2 |
|    8 |   88 |    1 |
|    9 |   87 |    9 |
|   10 |   87 |    7 |
|   11 |   80 |   10 |
+------+------+------+
11 rows in set (0.00 sec)

db2> select t1.*
    -> from tb_a t1,tb_a t2
    -> where t1.a=t2.a-1
    -> and t1.b-t1.c != t2.b;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    5 |   93 |    1 |
|    9 |   87 |    9 |
+------+------+------+
2 rows in set (0.05 sec)

db2>

------解决方案--------------------
如果不连号

SQL code

db2> select *
    -> from tb_a t1
    -> where b-c != (select b from tb_a where a=(select min(a) from tb_a where a>t1.a));
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    5 |   93 |    1 |
|    9 |   87 |    9 |
+------+------+------+
2 rows in set (0.02 sec)

db2>

------解决方案--------------------

SQL code

SELECT a,b,c from tb_a as s
where ( select  last(t.b-t.c-s.b) from tb_a as t where s.a>t.a)<>0
or ( ( select  first(t.b-t.c-s.b) from tb_a as t where s.a=t.a and s.c<>t.c )<>0
   and s.c=(select  last( t.c ) from tb_a as t where s.a=t.a  )
)

------解决方案--------------------
SELECT a, b, c
FROM tb_a AS s
WHERE (( select  last(t.b-t.c-s.b) from tb_a as t where s.a>t.a)<>0
  and s.c=(select  first( t.c ) from tb_a as t where s.a=t.a  )
)

or ( ( select  first(t.b-t.c-s.b) from tb_a as t where s.a=t.a and s.c<>t.c )<>0
  and s.c=(select  last( t.c ) from tb_a as t where s.a=t.a  )
);

------解决方案--------------------
第一次测试的时候，就发现这个问题，就是取自连接当中A、B两表的字段，
关键是编号问题，
有唯一标识的字段，用查询生成，否则增加自增字段

------解决方案--------------------
没有唯一标识的字段，可以增加一个自增字段，
OR
用排序（窗口函数）增加，这是前提，否则用游标解决

一个不知是否有解的SQL解决方法

相关推荐