select max(id) as mid,min(id) as mid from( select px = row_number() over (order by getdate()),id from tb )t group by (px - id)
px id 分别代表什么?
create table tb(BH int) insert into tb select 1 union select 2 union select 3 union select 11 union select 12 union select 13 union select 21 union select 22 union select 23 union select 25
declare @num int,@num2 int select @num=0,@num2=0 select max(BH) as mad,min(BH) as mid from( select px = row_number() over (order by getdate()),BH from tb )t group by (px - BH) order by 1/* mad mid ----------- ----------- 3 1 13 11 23 21 25 25
还有,我的是sql2000不支持row_number着个命令
create table tb(BH int) insert into tb select 1 union select 2 union select 3 union select 11 union select 12 union select 13 union select 21 union select 22 union select 23 union select 25 select px = (select count(1) from tb where BH<=a.BH), BH into #tb from tb aselect max(BH) as mad,min(BH) as mid from #tb group by (px - BH) order by 1drop table #tb/* mad mid ----------- ----------- 3 1 13 11 23 21 25 25
select max(id) as mid,min(id) as mid
from(
select px = row_number() over (order by getdate()),id
from tb
)t
group by (px - id)
insert into tb
select 1 union select 2 union select 3 union
select 11 union select 12 union select 13 union
select 21 union select 22 union select 23 union
select 25
declare @num int,@num2 int
select @num=0,@num2=0
select max(BH) as mad,min(BH) as mid
from(
select px = row_number() over (order by getdate()),BH
from tb
)t
group by (px - BH) order by 1/*
mad mid
----------- -----------
3 1
13 11
23 21
25 25
insert into tb
select 1 union select 2 union select 3 union
select 11 union select 12 union select 13 union
select 21 union select 22 union select 23 union
select 25 select px = (select count(1) from tb where BH<=a.BH),
BH into #tb
from tb aselect max(BH) as mad,min(BH) as mid
from #tb
group by (px - BH) order by 1drop table #tb/*
mad mid
----------- -----------
3 1
13 11
23 21
25 25