select *
from INFORMATION_SCHEMA.TABLES
where ISNUMERIC(table_name) = 1 and CAST(table_name as int) <> 0
这可能会失败,因为在确定table_name字段是否为数字之前,CAST可能已经被评估(因此对于无效的转换抛出错误)。
肯定有一种方法可以实现这个吧?
select *
from INFORMATION_SCHEMA.TABLES
where ISNUMERIC(table_name) = 1 and CAST(table_name as int) <> 0
SELECT *
FROM (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE ISNUMERIC(table_name)=1
) AS i
WHERE CAST(table_name AS INT)<>0
或者,最可能按顺序运行的是使用CASE语句:
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE 0<>(CASE WHEN ISNUMERIC(table_name)=1
THEN CAST(table_name AS INT)
ELSE 0 END)
需要注意的是,对于 SQL Server,存在一些情况下 CASE 技巧将会失效。请参阅 CASE 的文档备注:
The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.
WITH Data (value) AS ( SELECT 0 UNION ALL SELECT 1 ) SELECT CASE WHEN MIN(value) <= 0 THEN 0 WHEN MAX(1/value) >= 100 THEN 1 END FROM Data ;
我怀疑其他关系型数据库管理系统的实现也可能是如此。
我想你在询问评估是否可以短路 - 阅读以下内容可能会有所帮助:
因此,在尝试第二个条件之前,您可能需要评估第一个条件。在MSSQL中,您可以使用CTE来执行第一个条件。或者,另一个选择是使用CASE仅在特定条件下执行第二个条件。