求一个SQL语句,查询最接近的一个数:
比如表 a:id score
1 10
2 20
3 30
DECLARE @input INT输入 14 输出 1 10
输入 15 输出 2 20
输入 4 输出 1 10
输入 100 输出 3 30请大家帮帮忙啊
比如表 a:id score
1 10
2 20
3 30
DECLARE @input INT输入 14 输出 1 10
输入 15 输出 2 20
输入 4 输出 1 10
输入 100 输出 3 30请大家帮帮忙啊
(
id int ,
data bigint
)
insert into a select '1', '10'
union all select '2', '20'
union all select '3','30'
select * from adeclare @a bigint
set @a = 15
SELECT *
FROM a
WHERE id =
( SELECT
CASE
WHEN l.data-@a>=s.data-@a
THEN l.id
ELSE s.id
END
FROM
( SELECT top 1 *
FROM a
WHERE data >= @a
ORDER BY data ASC
) l,
( SELECT top 1 *
FROM a
WHERE data <= @a
ORDER BY data DESC
) s
)
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
create table tb
(
id int ,
data int
)
insert into tb select 1, 10
union all select 2, 20
union all select 3,30
go
declare @n int
set @n=15
select *
from tb
where ABS(data-@n) in (select top 1 abs(data-@n) from tb order by ABS(data-@n) )
go
/*
(3 行受影响)
id data
----------- -----------
1 10
2 20*/
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
create table tb
(
id int ,
data int
)
insert into tb select 1, 10
union all select 2, 20
union all select 3,30
go
declare @n int
set @n=15
select *
from tb k
where not exists (select * from tb where ABS(data-@n)<ABS(k.data-@n) )go
/*
(3 行受影响)
id data
----------- -----------
1 10
2 20*/
这样效率高点~
set @input = ( select top 1 score
from tb
order by ( @input - score ) )
set @input = ( select top 1 score
from tb
order by abs( @input - score ) )
INSERT INTO A(score)
SELECT 10 UNION ALL
SELECT 20 UNION ALL
SELECT 30 UNION ALL
SELECT -5 UNION ALL
SELECT -20;DECLARE @input INTSET @input=15
SELECT * FROM a s1
WHERE ABS(s1.score-@input)=(SELECT MIN(ABS(score-@input)) FROM a )
CREATE TABLE a(id INT IDENTITY(1,1), score INT);
INSERT INTO A(score)
SELECT 10 UNION ALL
SELECT 20 UNION ALL
SELECT 30 UNION ALL
SELECT -5 UNION ALL
SELECT -20;declare @insert int
set @insert=12select top 1 score from a order by abs(@insert-score)10
--补充楼上的
CREATE TABLE a(id INT IDENTITY(1,1), score INT);
INSERT INTO A(score)
SELECT 10 UNION ALL
SELECT 20 UNION ALL
SELECT 30 UNION ALL
SELECT -5 UNION ALL
SELECT -20;declare @insert int
set @insert=15select score from (select *,abs(@insert-score) n from a) tb where n=(select top 1 abs(@insert-score) m from a order by m)
select score from (select *,abs(@insert-score) n from a) tb where n=(select top 1 abs(@insert-score) m from a order by m)