table_a:aid num
11 1
12 2
13 3
14 4
15 5
19 13
21 30
50 60
... ...上面是数据库中的一张表,我现在想要得到的结果是: 给出一个数字,比如这个数字是12,那么我想通过一种算法找出和12相差最近的那个数字13,同样要是给的数字是55,那个得到是60这条记录大家对这个有什么好的idea,谢谢
11 1
12 2
13 3
14 4
15 5
19 13
21 30
50 60
... ...上面是数据库中的一张表,我现在想要得到的结果是: 给出一个数字,比如这个数字是12,那么我想通过一种算法找出和12相差最近的那个数字13,同样要是给的数字是55,那个得到是60这条记录大家对这个有什么好的idea,谢谢
(select min(abs(aid-12)) col from tb) n
where abs(m.aid - 12) = n.col
select m.* from table_a m ,
(select min(abs(num-12)) col from tb) n
where abs(m.num - 12) = n.col
insert into tb values(11, 1 )
insert into tb values(12, 2 )
insert into tb values(13, 3 )
insert into tb values(14, 4 )
insert into tb values(15, 5 )
insert into tb values(19, 13)
insert into tb values(21, 30)
insert into tb values(50, 60)
godeclare @num as int
set @num = 12select m.* from tb m ,
(select min(abs(num-@num)) col from tb) n
where abs(m.num - @num) = n.col
/*
aid num
----------- -----------
19 13(所影响的行数为 1 行)
*/set @num = 55
select m.* from tb m ,
(select min(abs(num-@num)) col from tb) n
where abs(m.num - @num) = n.col
/*
aid num
----------- -----------
50 60(所影响的行数为 1 行)
*/drop table tb
create table table_a(aid int, num int)
insert into table_a values(11, 1 )
insert into table_a values(12, 2 )
insert into table_a values(13, 3 )
insert into table_a values(14, 4 )
insert into table_a values(15, 5 )
insert into table_a values(19, 13)
insert into table_a values(21, 30)
insert into table_a values(50, 60)
godeclare @num int
set @num=12
select num from table_a a
where not exists(select 1 from table_a where abs(@num-num)<abs(@num-a.num))