f0 f1 f2 f3 f4 f5
0 0 16 0 9 0
1 0 16 0 9 1
2 0 16 0 9 2
3 0 16 0 9 3
4 0 16 0 9 4
5 0 16 0 9 5
6 0 16 0 9 6
7 0 16 0 10 7
8 0 16 0 9 8
9 0 16 0 9 9
10 0 16 0 10 10
11 0 16 0 10 11
12 0 16 0 10 12
13 0 16 0 10 13
14 0 16 0 10 14
15 0 16 0 10 15
16 0 16 0 10 16我想使用sql选择f4=10 时f5最小的记录,注意:只能选择f4=10的时候后面所有的f4=10的记录
例如现在这种情况,就不能选择f0=7这条记录,因为后面还有f4=9的记录
0 0 16 0 9 0
1 0 16 0 9 1
2 0 16 0 9 2
3 0 16 0 9 3
4 0 16 0 9 4
5 0 16 0 9 5
6 0 16 0 9 6
7 0 16 0 10 7
8 0 16 0 9 8
9 0 16 0 9 9
10 0 16 0 10 10
11 0 16 0 10 11
12 0 16 0 10 12
13 0 16 0 10 13
14 0 16 0 10 14
15 0 16 0 10 15
16 0 16 0 10 16我想使用sql选择f4=10 时f5最小的记录,注意:只能选择f4=10的时候后面所有的f4=10的记录
例如现在这种情况,就不能选择f0=7这条记录,因为后面还有f4=9的记录
和
sql选择f4=10 时f5最小的记录 (这不就是f0=7的记录吗?)两句话理解 看示例数据 好象有冲突
--------------select * from tb
where f4=10 and f5=(select min(f5) from tb where f4=10)
所以记录f0=7不能选择,因为满足f0>=7中的记录中有f4不为10的记录
而又出现 f0=18 f4=10 ,后面又有连续的几个f4=10
这个时候怎么处理?
只能选择F0=18,我的记录是循环出现的,所以现在可以假设这些数据再数据库中循环出现,按照ID自动编号保存再数据库中。现在的要求是使用SQL把一个循环中的满足我上面说的记录找出来,
from tb a
where f0=10
and (select min(自增列) from tb where f0=10 and 自增列>a.自增列)
< (select min(自增列) from tb where f0!=10 and 自增列>a.自增列)
order by 自增列
insert @tb
select 0,0,16,0,9,0 union
select 1,0,16,0,9,1 union
select 2,0,16,0,9,2 union
select 3,0,16,0,9,3 union
select 4,0,16,0,9,4 union
select 5,0,16,0,9,5 union
select 6,0,16,0,9,6 union
select 7,0,16,0,10,7 union
select 8,0,16,0,9,8 union
select 9,0,16,0,9,9 union
select 10,0,16,0,10,10 union
select 11,0,16,0,10,11 union
select 12,0,16,0,10,12 union
select 13,0,16,0,10,13 union
select 14,0,16,0,10,14 union
select 15,0,16,0,10,15 union
select 16,0,16,0,10,16select *,identity(int,1,1) id
into #tmp
from @tbselect min(a.f5)
from #tmp a,#tmp b
where a.f4 = 10 and b.f4 = 10
and a.id + 1 = b.iddrop table #tmp
insert @tb
select 0,0,16,0,9,0 union
select 1,0,16,0,9,1 union
select 2,0,16,0,9,2 union
select 3,0,16,0,9,3 union
select 4,0,16,0,9,4 union
select 5,0,16,0,9,5 union
select 6,0,16,0,9,6 union
select 7,0,16,0,10,7 union
select 8,0,16,0,9,8 union
select 9,0,16,0,9,9 union
select 10,0,16,0,10,10 union
select 11,0,16,0,10,11 union
select 12,0,16,0,10,12 union
select 13,0,16,0,10,13 union
select 14,0,16,0,10,14 union
select 15,0,16,0,10,15 union
select 16,0,16,0,10,16select *
from @tb a
where f4=10 and not exists(select top 1 * from @tb where f4<>10 and f0>a.f0)