Sql как найти ближайшее значение

asked Feb 26, 2009 at 20:07

If you have many rows that satisfy the equality predicates on Name, Size, and PType columns then you may want to include range predicates on the Area column in your query. If the Area column is indexed this could allow efficient index-based access.

The following query (written using Oracle syntax) uses one branch of a UNION ALL to find the record with minimal Area >= your target, while the other branch finds the record with maximal Area < your target. One of these two records will be the record that you are looking for. Then you can ORDER BY ABS(Area - ?input) to pick the winner out of those two candidates. Unfortunately the query is complex due to nested SELECTS that are needed to enforce the desired ROWNUM / ORDER BY precedence.

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

A good index for this query would be (Name, Size, PType, Area), in which case the expected query execution plan would be based on two index range scans that each returned a single row.

answered Feb 27, 2009 at 17:13

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

— MarkusQ

answered Feb 26, 2009 at 20:14

Note that although ABS() is supported by pretty much everything, it’s not technically standard (in SQL99 at least). If you must write ANSI standard SQL for some reason, you’d have to work around the problem with a CASE operator:

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

answered Feb 26, 2009 at 20:29

If using MySQL

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

answered Feb 26, 2009 at 20:13

Здравствуйте.
Суть задачи получить товары с близкой ценой от текущего товара (Рекомендуемые товары). Сайт на OpenCart.

К примеру текущая цена товара 50$, и нужно что бы показывались товары с самой близкой ценой, а не 100$ и не 10$.

Нашел запрос, получающий id товаров с рубрики. Однако нужно получить близкие товары по цене.
Запрос:

$query = $this->db->query("SELECT DISTINCT(p2c.product_id) FROM `" . DB_PREFIX . "product_to_category` p2c LEFT JOIN `" . DB_PREFIX . "product` p ON (p2c.product_id = p.product_id) LEFT JOIN `" . DB_PREFIX . "product_to_store` p2s ON (p.product_id = p2s.product_id) WHERE p2c.category_id IN ('" . $category_list . "') AND p2c.product_id != '" . (int)$product_id . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY RAND() LIMIT " . $limit);

Цена находится в таблице product и столбец price

Задачу можно решить в рамках одного SELECT. Достаточно добавить такую сортировку:

SELECT ...
FROM ...
WHERE ...
ORDER BY
  CASE
    WHEN price < 'ваша стоимость'
      THEN price + (('ваша стоимость' - price) * 2)
      ELSE price
  END ASC
LIMIT N

ответ дан 6 мая 2017 в 21:57