如何计算Oracle SQL select中两个数字的最大值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3521124/
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
How to calculate the maximum of two numbers in Oracle SQL select?
提问by Peter G.
This should be simple and shows my SQL ignorance:
这应该很简单，显示了我对 SQL 的无知：
SQL> select max(1,2) from dual;
select max(1,2) from dual
*
ERROR at line 1:
ORA-00909: invalid number of arguments
I know max is normally used for aggregates. What can I use here?
我知道 max 通常用于聚合。我可以在这里使用什么？
In the end, I want to use something like
最后，我想使用类似的东西
select total/max(1,number_of_items) from xxx;
where number_of_items is an integer and can be 0. I want to see total also in this case.
其中 number_of_items 是一个整数，可以是 0。在这种情况下，我也想查看总数。
采纳答案by Lieven Keersmaekers
回答by Martin Smith
It looks like you're using Oracle so you can use the greatestfunction for this in place of max
看起来您正在使用 Oracle，因此您可以使用该greatest函数代替max
select total/greatest(1,number_of_items)
from xxx;
回答by Rod Meyer
As of Oracle 10.2 they introduced a GREATEST function which does what you want. There is also a LEAST function too.
从 Oracle 10.2 开始，他们引入了一个 GREATEST 函数，可以满足您的需求。还有一个 LEAST 函数。
Examples:
例子：
select greatest(1,2) from dual;
select greatest(1,2) from dual;
GREATEST(1,2) ------------- 2
GREATEST(1,2) ------------- 2
select greatest(8,6,4,2) from dual;
select greatest(8,6,4,2) from dual;
GREATEST(8,6,4,2) ----------------- 8
GREATEST(8,6,4,2) ----------------- 8
select greatest(-1,-2) from dual;
select greatest(-1,-2) from dual;
GREATEST(-1,-2) --------------- -1
GREATEST(-1,-2) --------------- -1
select greatest('A','B','CCC','D') from dual;
select greatest('A','B','CCC','D') from dual;
GREATEST('A','B','CCC','D') --------------- D
GREATEST('A','B','CCC','D') --------------- D
回答by Hari Menon
SELECT total/(CASE WHEN number_of_items>1 THEN number_of_items ELSE 1 END) FROM xxx
should work here.......
应该在这里工作......
回答by Uri Abramson
You'll have to create a new function for this:
您必须为此创建一个新函数：
CREATE FUNCTION InlineMax
(
@p1 sql_variant,
@p2 sql_variant
) RETURNS sql_variant
AS
BEGIN
RETURN
CASE
WHEN @p1 IS NULL AND @p2 IS NOT NULL THEN @p2
WHEN @p2 IS NULL AND @p1 IS NOT NULL THEN @p1
WHEN @p1 > @p2 THEN @p1
ELSE @p2 END
END;
Checkout this thread for more details: Is there a Max function in SQL Server that takes two values like Math.Max in .NET?
查看此线程以获取更多详细信息：SQL Server 中是否有一个 Max 函数，它采用两个值，例如 .NET 中的 Math.Max？
回答by wardies
It is possible to do this in Oracle 8.0 and older (i.e. before CASEwas introduced) with the following mathematical trick:
可以CASE使用以下数学技巧在 Oracle 8.0 及更早版本（即之前引入）中执行此操作：
SELECT DECODE(NUMBER_OF_ITEMS-1+ABS(NUMBER_OF_ITEMS-1), 0, 1, NUMBER_OF_ITEMS) AS TOTAL
FROM xxx
... which is equivalent to max(1,number_of_items).
... 相当于max(1,number_of_items).
Replace the three 1s above with another value as required.
1根据需要将上面的三个s替换为另一个值。
This works because number_of_items - 1goes zero or negative when number_of_items is less than 1. And in general, x + abs(x)is always zero when x <= 0, so the first decodeoption is matched.
这是有效的，因为number_of_items - 1当 number_of_items 小于 1 时为零或负数。通常，x + abs(x)当 x <= 0 时始终为零，因此decode匹配第一个选项。
We need this because some of our (3rd party) customers may still be using Oracle 8.0 and it would be many days of effort to find out if or when the last customer will finally upgrade!
我们需要这样做是因为我们的一些（第 3 方）客户可能仍在使用 Oracle 8.0，而且要找出最后一个客户是否或何时最终升级需要花费很多天的时间！
回答by XstreamINsanity
Normally it would be:
通常它会是：
SELECT MAX(columnName)
FROM Table1
Or
或者
SELECT MAX(columnName)
FROM (SELECT * FROM TableX) AS T1
Or (and this would probably be what you want in your case)
或者（这可能就是你想要的）
SELECT MAX(value)
FROM (SELECT 1 AS VALUE FROM DUAL UNION SELECT 2 AS VALUE FROM DUAL)
There may be a cleaner way to do it though.
不过，可能有一种更清洁的方法来做到这一点。
UPDATE: Using your example of number_of_items and total from table XXX, it'd be:
更新：使用您的 number_of_items 和表 XXX 中的总数示例，它将是：
SELECT TOTAL/MAX(NUMBER_OF_ITEMS)
FROM XXX
UPDATE 2: Keep in mind, if you allow number of items to be 0, you will get an exception of division by 0. That's why in the other answer the user put a case and the else was the TOTAL, this way you don't get that exception.
更新 2：请记住，如果您允许项目数为 0，您将得到除以 0 的异常。这就是为什么在另一个答案中用户放置了一个案例，而 else 是 TOTAL，这样您就不会“没有得到那个例外。
