表格如下:id kb
1 100
2 120
3 260
4 480
5 800输入80 查出 id = 1 这条
输入130 查出 id = 2 这条
输入900 查出 id = 5 这条请问怎么写SQL
1 100
2 120
3 260
4 480
5 800输入80 查出 id = 1 这条
输入130 查出 id = 2 这条
输入900 查出 id = 5 这条请问怎么写SQL
(select min(abs(kb-@kb)) abs_kb from tb)
insert into tb values(1 ,100)
insert into tb values(2 ,120)
insert into tb values(3 ,260)
insert into tb values(4 ,480)
insert into tb values(5 ,800)
godeclare @kb as intset @kb = 80
select * from tb where abs(kb-@kb) = (select min(abs(kb-@kb)) abs_kb from tb)
/*
id kb
----------- -----------
1 100(所影响的行数为 1 行)
*/set @kb = 130
select * from tb where abs(kb-@kb) = (select min(abs(kb-@kb)) abs_kb from tb)
/*
id kb
----------- -----------
2 120(所影响的行数为 1 行)
*/set @kb = 900
select * from tb where abs(kb-@kb) = (select min(abs(kb-@kb)) abs_kb from tb)
/*
id kb
----------- -----------
5 800(所影响的行数为 1 行)
*/drop table tb
select top 1 * from tb
order by abs(kb-@num)
--1、查询出两值kb-@kb,绝对值最小的值。
select min(abs(kb-@kb)) abs_kb from tb--2、查询两值kb-@kb,绝对值等于上面的那个最小值。
select * from tb where abs(kb-@kb) = (select min(abs(kb-@kb)) abs_kb from tb)
CREATE TABLE #
(id INT, kb INT)
INSERT INTO #
SELECT 1, 100 UNION ALL
SELECT 2, 120 UNION ALL
SELECT 3, 260 UNION ALL
SELECT 4, 480 UNION ALL
SELECT 5, 800
DECLARE @qty int
SET @qty=80SELECT * FROM #
WHERE id IN
(
SELECT TOP 1 ID FROM # ORDER BY abs(kb-@qty)
)SET @qty=130
SELECT * FROM #
WHERE id IN
(
SELECT TOP 1 ID FROM # ORDER BY abs(kb-@qty)
)
SET @qty=900
SELECT * FROM #
WHERE id IN
(
SELECT TOP 1 ID FROM # ORDER BY abs(kb-@qty)
)/*
id kb
----------- -----------
1 100(1 行受影响)id kb
----------- -----------
2 120(1 行受影响)id kb
----------- -----------
5 800(1 行受影响)
*/