Найти ближайшее числовое значение в базе данных

Мне нужно найти инструкцию select, которая вернет либо запись, которая соответствует моему вводу, либо ближайшему совпадению, если точное совпадение не найдено.

Вот мой оператор выбора.

SELECT * FROM [myTable] 
WHERE Name = 'Test' AND Size = 2 AND PType = 'p' 
ORDER BY Area DESC

Мне нужно найти ближайшее соответствие к полю 'Area', поэтому, если мой ввод равен 1.125, а база данных содержит 2, 1.5, 1 и .5, запрос вернет запись, содержащую 1.

Мои навыки SQL очень ограничены, поэтому любая помощь будет оценена.

Ответ 1

получить разницу между областью и вашим входом, принять абсолютное значение, всегда положительное, а затем упорядочить по возрастанию и взять первый

SELECT TOP 1 * FROM [myTable] 
WHERE Name = 'Test' and Size = 2 and PType = 'p'
ORDER BY ABS( Area - @input )

Ответ 2

что-то ужасное, по строкам:

ORDER BY ABS( Area - 1.125 ) ASC LIMIT 1

Может быть,?

Ответ 3

Как насчет упорядочения по разнице между вашим входом и [Area], например:

DECLARE @InputValue DECIMAL(7, 3)

SET @InputValue = 1.125

SELECT TOP 1 * FROM [myTable]
WHERE Name = 'Test' AND Size = 2 AND PType = 'p'
ORDER BY ABS(@InputValue - Area)

Ответ 4

SELECT * 
  FROM [myTable] 
  WHERE Name = 'Test' AND Size = 2 AND PType = 'p' 
  ORDER BY ABS(Area - 1.125)
  LIMIT 1

- MarkusQ

Ответ 5

Если у вас много строк, которые удовлетворяют предикатам равенства на столбцах Name, Size и PType, то вы можете включить в предикат диапазона в столбце Area в своем запросе. Если индекс Area индексирован, это может обеспечить эффективный доступ на основе индексов.

Следующий запрос (написанный с использованием синтаксиса Oracle) использует одну ветвь UNION ALL для поиска записи с минимальной Area >= вашей целью, а другая ветка находит запись с максимальной Area < вашей целью. Одной из этих двух записей будет запись, которую вы ищете. Тогда вы можете ORDER BY ABS(Area - ?input) выбрать победителя из этих двух кандидатов. К сожалению, запрос сложный из-за вложенных SELECTS, которые необходимы для обеспечения желаемого приоритета ROWNUM/ORDER BY.

SELECT *
FROM
  (SELECT * FROM
    (SELECT * FROM
       (SELECT * FROM [myTable]
         WHERE Name = 'Test' AND Size = 2 AND PType = 'p' AND Area >= ?target
         ORDER BY Area)
       WHERE ROWNUM < 2
     UNION ALL
     SELECT * FROM 
       (SELECT * FROM [myTable]
         WHERE Name = 'Test' AND Size = 2 AND PType = 'p' AND Area < ?target
         ORDER BY Area DESC)
       WHERE ROWNUM < 2)
     ORDER BY ABS(Area - ?target))
WHERE rownum < 2

Хорошим индексом для этого запроса будет (Name, Size, PType, Area), и в этом случае ожидаемый план выполнения запроса будет основан на двух сканированиях диапазона индексов, каждый из которых возвращает одну строку.

Ответ 6

Обратите внимание, что хотя ABS() поддерживается почти всем, это не технически стандартно (по крайней мере, в SQL99). Если по какой-то причине вы должны написать стандартный SQL ANSI, вам придется обойти проблему с оператором CASE:

SELECT * FROM myTable
WHERE Name='Test' AND Size=2 AND PType='p'
ORDER BY CASE Area>1.125 WHEN 1 THEN Area-1.125 ELSE 1.125-Area END

Ответ 7

Если вы используете MySQL

SELECT * FROM [myTable] ... ORDER BY ABS(Area - SuppliedValue) LIMIT 1

Ответ 8

Выберите min, где [field] > your_target_value Выберите max, где [поле] < your_target_value