a b
4 37
32.5 40
32.5 40
32.5 37
51.3 39.8
51.3 54.55
51.3 33.5
76.2 46
76.2 50
44.75 30.7
38.86 31
44.75 28.8
44.75 30.7
44.75 30.7
...
要求结果如下:
5,40
35,40
55,55
80,50
45,35
40,35
即a,b各取与其值最接近的5的倍数,如1取5, 5.5取10...
然后按a分组取b的最大值declare @test table (a numeric(9,4),b numeric(9,4))
insert into @test
select 4,37 union all
select 32.5,40 union all
select 32.5,40 union all
select 32.5,37 union all
select 51.3,39.8 union all
select 51.3,54.55 union all
select 51.3,33.5 union all
select 76.2,46 union all
select 76.2,50 union all
select 44.75,30.7 union all
select 38.86,31 union all
select 44.75,28.8 union all
select 44.75,30.7
select * from @test
4 37
32.5 40
32.5 40
32.5 37
51.3 39.8
51.3 54.55
51.3 33.5
76.2 46
76.2 50
44.75 30.7
38.86 31
44.75 28.8
44.75 30.7
44.75 30.7
...
要求结果如下:
5,40
35,40
55,55
80,50
45,35
40,35
即a,b各取与其值最接近的5的倍数,如1取5, 5.5取10...
然后按a分组取b的最大值declare @test table (a numeric(9,4),b numeric(9,4))
insert into @test
select 4,37 union all
select 32.5,40 union all
select 32.5,40 union all
select 32.5,37 union all
select 51.3,39.8 union all
select 51.3,54.55 union all
select 51.3,33.5 union all
select 76.2,46 union all
select 76.2,50 union all
select 44.75,30.7 union all
select 38.86,31 union all
select 44.75,28.8 union all
select 44.75,30.7
select * from @test
insert into @test
select 4,37 union all
select 32.5,40 union all
select 32.5,40 union all
select 32.5,37 union all
select 51.3,39.8 union all
select 51.3,54.55 union all
select 51.3,33.5 union all
select 76.2,46 union all
select 76.2,50 union all
select 44.75,30.7 union all
select 38.86,31 union all
select 44.75,28.8 union all
select 44.75,30.7
select * from @testselect CEILING(a/5)*5 as a,max(b) as b
from @test
group by CEILING(a/5)*5--结果
a b
--------------- -----------
5 37.0000
35 40.0000
40 31.0000
45 30.7000
55 54.5500
80 50.0000(所影响的行数为 6 行)
declare @test table (a numeric(9,4),b numeric(9,4))
insert into @test
select 4,37 union all
select 32.5,40 union all
select 32.5,40 union all
select 32.5,37 union all
select 51.3,39.8 union all
select 51.3,54.55 union all
select 51.3,33.5 union all
select 76.2,46 union all
select 76.2,50 union all
select 44.75,30.7 union all
select 38.86,31 union all
select 44.75,28.8 union all
select 44.75,30.7 select distinct a=ceiling(a/5)*5,b=ceiling(b/5)*5 from @test a where not exists(select 1 from @test where a=a.a and b>a.b)
--恩,老海的更简单:
declare @test table (a numeric(9,4),b numeric(9,4))
insert into @test
select 4,37 union all
select 32.5,40 union all
select 32.5,40 union all
select 32.5,37 union all
select 51.3,39.8 union all
select 51.3,54.55 union all
select 51.3,33.5 union all
select 76.2,46 union all
select 76.2,50 union all
select 44.75,30.7 union all
select 38.86,31 union all
select 44.75,28.8 union all
select 44.75,30.7 select a=ceiling(a/5)*5,b=ceiling(max(b)/5)*5 from @test a
group by a
insert into @test
select 4,37 union all
select 32.5,40 union all
select 32.5,40 union all
select 32.5,37 union all
select 51.3,39.8 union all
select 51.3,54.55 union all
select 51.3,33.5 union all
select 76.2,46 union all
select 76.2,50 union all
select 44.75,30.7 union all
select 38.86,31 union all
select 44.75,28.8 union all
select 44.75,30.7
select * from @testselect ceiling(a/5)*5 as a,max(ceiling(b/5)*5) as b
from @test
group by CEILING(a/5)*5