ORACLE - 在子查询上选择计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20387158/
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
ORACLE - Select Count on a Subquery
提问by user3067761
I've got an Oracle table that holds a set of ranges (RangeA and RangeB). These columns are varchar as they can hold both numeric and alphanumeric values, like the following example:
我有一个包含一组范围（RangeA 和 RangeB）的 Oracle 表。这些列是 varchar，因为它们可以保存数字和字母数字值，如下例所示：
ID|RangeA|RangeB
1 | 10 | 20
2 | 21 | 30
3 | AB50 | AB70
4 | AB80 | AB90
I need to to do a query that returns only the records that have numeric values, and perform a Count on that query. So far I've tried doing this with two different queries without any luck:
我需要执行一个查询，只返回具有数值的记录，并对该查询执行计数。到目前为止，我已经尝试使用两个不同的查询来执行此操作，但没有任何运气：
Query 1:
查询 1：
SELECT COUNT(*) FROM (
SELECT RangeA, RangeB FROM table R
WHERE upper(R.RangeA) = lower(R.RangeA)
) A
WHERE TO_NUMBER(A.RangeA) <= 10
Query 2:
查询 2：
WITH A(RangeA,RangeB) AS(
SELECT RangeA, RangeB FROM table
WHERE upper(RangeA) = lower(RangeA)
)
SELECT COUNT(*) FROM A WHERE TO_NUMBER(A.RangeA) <= 10
The subquery is working fine as I'm getting the two records that have only numeric values, but the COUNT part of the query is failing. I should be getting only 1 on the count, but instead I'm getting the following error:
子查询工作正常，因为我得到了只有数值的两条记录，但查询的 COUNT 部分失败了。我应该只得到 1 计数，但我收到以下错误：
ORA-01722: invalid number
01722. 00000 - "invalid number"
What am I doing wrong? Any help is much appreciated.
我究竟做错了什么？任何帮助深表感谢。
回答by Gordon Linoff
Try this query:
试试这个查询：
SELECT COUNT(*)
FROM table R
WHERE translate(R.RangeA, 'x0123456789', 'x') = 'x' and
translate(R.RangeB, 'x0123456789', 'x') = 'x'
First, you don't need the subquery for this purpose. Second, using to_number()or upper()/lower()are prone to other problems. The function translate()replaces each character in the second argument with values from the third argument. In this case, it removes numbers. If nothing is left over, then the original value was an integer.
首先，您不需要为此目的的子查询。其次，使用to_number()或upper()/lower()容易出现其他问题。该函数translate()用第三个参数中的值替换第二个参数中的每个字符。在这种情况下，它会删除数字。如果没有留下任何东西，则原始值是一个整数。
You can do more sophisticated checks for negative values and floating point numbers, but the example in the question seemed to be about positive integer values.
您可以对负值和浮点数进行更复杂的检查，但问题中的示例似乎与正整数值有关。
回答by MT0
You can test each column with a regular expression to determine if it is a valid number:
您可以使用正则表达式测试每一列以确定它是否为有效数字：
SELECT COUNT(1)
FROM table_of_ranges
WHERE CASE WHEN REGEXP_LIKE( RangeA, '^-?\d+(\.\d*)?$' )
THEN TO_NUMBER( RangeA )
ELSE NULL END
< 10
AND REGEXP_LIKE( RangeB, '^-?\d+(\.\d*)?$' );
Another alternative is to use a user-defined function:
另一种选择是使用用户定义的函数：
CREATE OR REPLACE FUNCTION test_Number (
str VARCHAR2
) RETURN NUMBER DETERMINISTIC
AS
invalid_number EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_number, -6502);
BEGIN
RETURN TO_NUMBER( str );
EXCEPTION
WHEN invalid_number THEN
RETURN NULL;
END test_Number;
/
Then you can do:
然后你可以这样做：
SELECT COUNT(*)
FROM table_of_ranges
WHERE test_number( RangeA ) <= 10
AND test_number( RangeB ) IS NOT NULL;
回答by mathguy
Coming to this question almost four years later (obviously, pointed here from a much newer thread). The other answers show how to achieve the desired output, but do not answer the OP's question, which was "what am I doing wrong?"
将近四年后才开始讨论这个问题（显然，这里是从一个更新的线程中指出的）。其他答案显示了如何实现所需的输出，但没有回答 OP 的问题，即“我做错了什么？”
You are not doing anything wrong. Oracle is doing something wrong. It is "pushing" the predicate (the WHEREcondition) from the outer query into the inner query. Pushing predicates is one of the most basic ways in which the Optimizer makes queries more efficient, but in some cases (and the question you ask is a PERFECT illustration) the result is not, in fact, logically equivalent to the original query.
你没有做错任何事。Oracle 做错了。它将谓词（WHERE条件）从外部查询“推送”到内部查询中。推送谓词是优化器提高查询效率的最基本方式之一，但在某些情况下（您提出的问题是一个完美的例证），结果实际上在逻辑上并不等同于原始查询。
There are ways to prevent the Optimizer from pushing predicates; or you can write the query in a better way (as shown in the other answers). But if you wanted to know why you saw what you saw, this is why.
有一些方法可以防止优化器推送谓词；或者您可以以更好的方式编写查询（如其他答案所示）。但是如果你想知道为什么你看到了你所看到的，这就是原因。
