oracle 查找表中大于某个值的最小值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/769069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding the lowest value in a table greater than a certain value
提问by Greg Reynolds
Say I have the following data
说我有以下数据
Name Value
===============
Small 10
Medium 100
Large 1000
Imagine that these represent the volumes of boxes. I have some items that I want to put in the boxes, and I want the smallest box possible. I need an SQL query that will:
想象一下，这些代表盒子的体积。我有一些东西想放在盒子里，我想要尽可能小的盒子。我需要一个 SQL 查询，它将：
- Return the row with the smallest row greater than my query parameter
- If there is no such row, then return the largest row.
- 返回最小行大于我的查询参数的行
- 如果没有这样的行，则返回最大的行。
It is easy to split this up in to two queries (i.e. query point 1 first and if no rows are returned, select the largest number from the table). However, I like to do things in one query if possible to eliminate overhead (both code and context switching), and it looks like it should be possible to do. It's probably very obvious, but the Sun has been shining on me all day and I can't think!
很容易将其拆分为两个查询（即首先查询点 1，如果没有返回行，则从表中选择最大的数）。但是，如果可能的话，我喜欢在一个查询中做一些事情以消除开销（代码和上下文切换），而且看起来应该可以做到。这可能很明显，但是太阳一整天都照在我身上，我无法思考！
So for example, I want the query to return 10 if you use a parameter of 5, 100 if you use a parameter of 15 and 1000 if you use anything greater than 100 (including numbers greater than 1000).
例如，如果您使用 5 的参数，我希望查询返回 10，如果您使用 15 的参数，则返回 100，如果您使用大于 100 的任何值（包括大于 1000 的数字），我希望查询返回 1000。
I'm on Oracle 11g, so any special Oracle goodness is OK.
我使用的是 Oracle 11g，所以任何特殊的 Oracle 优点都可以。
采纳答案by Quassnoi
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).
这都将有效地在使用索引mytable(value)和COUNT(STOPKEY)。
See this article in my blog for performance details:
有关性能详细信息，请参阅我博客中的这篇文章：
回答by dkretz
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.
只是为了好玩，我假设目标尺寸来自包裹表，并且您想找到一堆包裹的盒子。如果第一个值为 NULL，则 COALESCE 选择第二个值。
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
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
SELECT COALESCE(MIN(b1.size), MAX(b2.size) AS boxsize FROM Table AS t1, Table AS t2 WHERE targetsize < t1.Value
回答by Welbog
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.
我对 Oracle 不太熟悉，但我确定它有一个 LEAST 函数或等效的函数。
In any case, this query's subquery will be swift with the right index on the Valuecolumn.
在任何情况下，此查询的子查询都将在Value列上使用正确的索引快速执行。
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 alwaysor just when necessary.
严肃地说，您确实应该在两个查询中执行此操作（如果您想将它们保留在同一位置，则在一个存储过程中的两个步骤），因为如果第一个查询有效，则不需要第二个查询。将它们组合在一个查询中必然会给您一个无条件的第二个（或子）查询。您必须查询该表两次，因此问题是您是否始终或仅在必要时查询它两次。
回答by Brian
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)
回答by JosephStyons
This works. Replace the "5" with your parameter.
这有效。用您的参数替换“5”。
select min(basket_value) as basket_value
from baskets
where basket_value > 5
or basket_value = (select max(basket_value) from baskets)
Simple script to generate test data:
生成测试数据的简单脚本：
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
回答by James Curran
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
