MySQL: Как написать запрос который ищет ближайшее по условию значение?

Question

[cat_crash]

Есть такая таблица

Количество |Вес| Цена

1 | 0,1 | 180

2 | 0,6 | 200

3 | 1,2 | 250

5 | 2 | 300


как написать запрос который выберет строку с НАИБОЛЬШИМ ближайшим к указанному значением колонки Количество например:

Количество = 4


Оно лежит между 3 и 5. Нужно выбрать наибольшее КОЛИЧЕСТВО из 3 и 5 т.е. запрос должен вернуть строчку:

5 | 2 | 300


P.s. Большими буквами писал ключевые моменты чтоб обратить внимание

Answer 1

[ztxn]

>>НАИБОЛЬШИМ ближайшим

where количество >: запрошенное_количество
order by количество limit 1

Comments

[cat_crash]

Тоже думал об этом, НО не подходит для такого набора данных
Количество |Вес| Цена
1 | 0,1 | 180
2 | 0,6 | 200
3 | 1,2 | 250

В таком случае я ожидаю что будет вернут
3 | 1,2 | 250

Наверное все-таки не очень правильно описал задачу

[ztxn]

Возможно вы имеете в виду «ближайшее значение, в случае неоднозначности вернуть наибольшее значение»

Тогда как-то так:

order abs(количество - :запрошенное количество), количество limit 1

[ztxn]

Или «равное, в случае отсутствия — ближайшее наибольшее, в случае отсутствия, ближайшее наименьшее»?
select * from ( select * from table where количество >=:запрошенное_количество order by количество limit 1 union all select * from table where количество <:запрошенное_количество order by количество desc limit 1 ) s order by количество desc limit 1

[ztxn]

переформатировал

select *
from (
  select * from table where количество >=:запрошенное_количество order by количество limit 1
  union all
  select * from table where количество <:запрошенное_количество order by количество desc limit 1
) s
order by количество desc limit 1

[cat_crash]

Вот окончательный правильный запрос, спасибо всем!
Обратите внимание на скобки — они все нужны иначе MySQL будет ругаться!
select * from ( (select * from pricelist where qty >=1 order by qty asc limit 1) union all (select * from pricelist where qty <1 order by qty desc limit 1) ) _result order by qty desc limit 1

Answer 2

[Mikhail Osher]

mysql> select * from test1;
+----+-------+--------+-------+
| id | count | weight | price |
+----+-------+--------+-------+
|  1 |     1 |    0.1 |   100 |
|  2 |     2 |    0.6 |   200 |
|  3 |     3 |    1.2 |   250 |
|  4 |     5 |      2 |   300 |
+----+-------+--------+-------+
4 rows in set (0.00 sec)

mysql> select * from test1 having (count-4)>=0 order by count desc limit 1;
+----+-------+--------+-------+
| id | count | weight | price |
+----+-------+--------+-------+
|  4 |     5 |      2 |   300 |
+----+-------+--------+-------+
1 row in set (0.00 sec)

Comments

[Mikhail Osher]

ой, order by count asc, а не desc ><

[ztxn]

А почему having а не where? У MySQL это особенность какая-то?

[Mikhail Osher]

А вы почитайте разницу HAVING и WHERE :)

[ztxn]

Читаю

Выражение HAVING может ссылаться на любой столбец или псевдоним, упомянутый в выражении select_expression. HAVING отрабатывается последним, непосредственно перед отсылкой данных клиенту, и без какой бы то ни было оптимизации. Не используйте это выражение для определения того, что должно быть определено в WHERE. Например, нельзя задать следующий оператор:

mysql> SELECT col_name FROM tbl_name HAVING col_name > 0;

Вместо этого следует задавать:

mysql> SELECT col_name FROM tbl_name WHERE col_name > 0;

www.mysql.ru/docs/man/SELECT.html

Здесь написан бред?

[Mikhail Osher]

SELECT *, (`count`+`weight`) AS `pew` FROM `test1` WHERE `pew` > 0;
SELECT *, (`count`+`weight`) AS `pew` FROM `test1` HAVING `pew` > 0;
Посмотрите что выдаст каждый из запросов.

[ztxn]

К сожалению посмотреть не могу, у меня под рукой нет ни одного экземпляра MySQL, могу лишь предположить что первый запрос не выполнится.

Но в исходном примере вы ведь поставили having вместо where явно не для испльзования этого случая? Вот и интересуюсь почему? Мне действительно не понятно. Вы можете ответить прямо, не намеками?

[Mikhail Osher]

Скажу так. Я использую where только для директных выборок по столбику. Тут по идее тоже надо where писать, но на автомате написал HAVING.

Answer 3

[Ramzeska]

Тогда вообще вот так:

(SELECT * FROM `good` WHERE qty >= 4 ORDER BY qty LIMIT 1)
UNION ALL
(SELECT * FROM `good` WHERE qty < 4 ORDER BY qty DESC LIMIT 1)
LIMIT 1

Спасибо что про union all подсказали — я уж и забыл что это как distinct работает.

Comments

[ztxn]

Не подумайте что вредничаю, но limit без сортировки может вернуть любое значение а нам нужно не любое, наибольшее :D

[Ramzeska]

Строки уже отсортированы через union. MySQL конечно удивительная весчь, но в данном случае такого поведения быть не должно.

[ztxn]

Упорядоченность объединяемых наборов документирована? Емнип — нет. Это сайд эффект. Закладываться на сайд эффекты, без особой на то нужды, врядли стоит ;)

Но я могу и ошибаться. С MySQL вполне может статься задокументировать какой нить сайд эффект, как например они это сделали документировав то, что в результате группировки получается отсортированный набор. Теперь поддерживают совместимость, предлагают кастыли вроде order by null, чтоб избегать оверхеда :D

[egorinsk]

Ксиати, с точки зрения использования индексов этот составной запрос вполне может оказаться намного эффективнее того, что в верхнем комментарии нагородили с ABS() (так как ABS не индексируется).

[ztxn]

Да, но они не эквивалентны.
Можно заэквивалентить добавив ордер бай по абсу к результату юниона.

Answer 4

[Ramzeska]

SELECT * FROM (SELECT * FROM `good` WHERE qty >= 4 ORDER BY qty >= 4, qty LIMIT 1) as g
UNION 
SELECT * FROM (SELECT * FROM `good` WHERE qty < 4 ORDER BY qty < 4, qty DESC LIMIT 1) as g

Выбираете всегда первую строчку и это будет наибольшим ближайшим от 4. Если нет большего — вернется ближайший меньший от 4.

Comments

[ztxn]

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

И почему union a не union all. Это ведь лишняя сортировка, а множества возвращаемые запросами никогда не пересекутся

Answer 5

[yupic]

Недавно решал похожую задачу в Access. У меня было две таблицы: таблица сделок, в которой был столбец с временем сделки, и таблица цен с колонками товар, время начала действия цены и цена.
Надо было определить по какой цене были произведены сделки. В обеих таблицах было очень много строк.
Перепробовал кучу вариантов: с подзапросами, промежуточными таблицами и пр. Но все они работали очень долго, так как для каждой строки из таблицы сделок надо было выполнять подзапрос с LIMIT 1.
В итоге самым быстрым оказалось такое решение: получить две таблицы, отсортировав их по времени, далее в цикле одновременно обходить обе таблицы и выполнять пересечение вручную.