Нахождение самого низкого значения в таблице, больше, чем определенное значение
То, что вы используете, это Multitable SELECT.
Multitable SELECT (M-SELECT)аналогичен операции соединения. Вы выбираете значения из разных таблиц, используете предложениеWHERE, чтобы ограничить количество возвращаемых строк, и отправляете полученную в результате единственную таблицу обратно отправителю запроса.Разница с
blockquote>M-SELECTзаключается в том, что он возвращает таблицы умножения в качестве набора результатов.
Для получения дополнительной информации: https://dev.mysql.com/worklog/task/?id=358Другими словами, вы запрашиваете является:
SELECT * FROM a CROSS JOIN b WHERE b.id in (1,2,3)
6 ответов
SELECT *
FROM (
SELECT *
FROM (
SELECT *
FROM mytable
WHERE value > 10000
ORDER BY
value
)
UNION ALL
SELECT *
FROM (
SELECT *
FROM mytable
ORDER BY
value DESC
)
)
WHERE rownum = 1
This will both efficiently use an index on mytable(value) and COUNT(STOPKEY).
See this article in my blog for performance details:
SELECT MAX(Value)
FROM Table
WHERE Value <= LEAST(@param,(SELECT MAX(Value) FROM Table))
I'm not that familiar with Oracle but I'm sure it has a LEAST function or something equivalent.
In any case, this query's subquery will be swift with the right index on the Value column.
In all seriousness you really should do this in two queries (or two steps in one stored procedure if you want to keep them in the same place), because the second query is unnecessary if the first query works. Combining them in one query necessarily gives you an unconditional second (or sub-) query. You have to query the table twice, so the question is whether you query it twice always or just when necessary.
Just for fun I made the assumption that the target sizes are coming from a table of packages and you want to find the boxes for a bunch of packages. COALESCE chooses the second value if the first is NULL.
SELECT p.pkgid, p.pkgsize, COALESCE(MIN(b1.size), MAX(b2.size) AS boxsize FROM packages AS p LEFT JOIN boxes AS b1 ON p.pkgsize < b1.boxsize LEFT JOIN boxes AS b2 -- yes, a cartesian join GROUP BY p.pkgid, p.pkgsize
As a single statement to compare to the other solutions, use
SELECT COALESCE(MIN(b1.size), MAX(b2.size) AS boxsize FROM Table AS t1, Table AS t2 WHERE targetsize < t1.Value
WITH ranges_table AS
(SELECT LEVEL * 100 AS range_value
FROM DUAL
CONNECT BY LEVEL <= 20)
SELECT MIN (range_value)
FROM ranges_table
WHERE range_value >= 5 OR range_value = (SELECT MAX (range_value)
FROM ranges_table)
select a.newvalue from (
select MIN(value) as newvalue, 1 as order From table where value > @param
union select MAX(value) as newvalue, 2 as order from table) A
order by a.order
Это работает. Замените «5» на ваш параметр.
select min(basket_value) as basket_value
from baskets
where basket_value > 5
or basket_value = (select max(basket_value) from baskets)
Простой скрипт для генерации тестовых данных:
create table baskets(
basket_name varchar2(20)
,basket_value number
);
insert into baskets(basket_name,basket_value) values('Small',10);
insert into baskets(basket_name,basket_value) values('Medium',100);
insert into baskets(basket_name,basket_value) values('Large',1000);
commit;
--drop table baskets; --run when finished