sql - 从表中获取所有行，其中列仅包含0

Question

我有点问题

我需要一个SQL查询，使所有返回的行仅包含0。

该列定义为varchar2(6)
列中的值如下所示:

Row      Value
1        0
2        00
3        00
4        100
5        bc00
6        000000
7        00000

我的第一个解决方案是这样的:

Oracle:
substr('000000' || COLUMN_NAME, -6) = '000000'

SQL Server:
right('000000' + COLUMN_NAME, 6) = '000000'

还有其他方法吗？
(它需要同时在两个系统上工作)

输出将是行1,2,3,6,7

Answer 1

这是最简单的一个:

select * from tbl where replace(col,'0','') = ''

如果不为该表达式创建计算列，则可以选择基于函数的索引(注意:Oracle和Postgres已经支持此功能； SQL Server从2008版开始，尚未支持)以提高性能:

create index ix_tbl on tbl(replace(col,'0',''))

[编辑]

我只是保留以下答案以供后代参考，我试图解释如何使查询使用计算列中的索引。

用这个:

select * from tbl
where ISNUMERIC(col) = 1 and cast(col as int) = 0

对于Oracle上的ISNUMERIC需求，请使用以下命令: http://www.oracle.com/technology/oramag/oracle/04-jul/o44asktom.html

[编辑]

@Charles，回复:Oracle上的计算列:

对于支持计算列但没有持久选项的RDBMS，是的，它将对每一行进行函数调用。如果它支持持久列，则不会进行函数调用，您在表上有从该函数预先计算的实列。现在，如果数据可以使函数引发异常，则有两种情况。

首先，如果您未指定persist，它将允许您保存计算列( ALTER TABLE tbl ADD numeric_equivalent AS cast(col as int))，即使数据结果将引发异常，但您也无法无条件选择该列，这将引发异常:

select * from tbl

这不会引发异常:

select * from tbl where is_col_numeric = 1

这将:

select * from tbl where numeric_equivalent = 0 and is_col_numeric = 1

这不会(Sql Server支持 short-circuiting):

select * from tbl where is_col_numeric = 1 and numeric_equivalent = 0 

作为参考，上面的is_col_numeric是使用以下方法创建的:

ALTER TABLE tbl ADD 
is_col_numeric  AS isnumeric(col)       

这是is_col_numeric的索引:

create index ix_is_col_numeric on tbl(is_col_numeric)

现在，对于第二种情况，将带有PERSISTED选项的计算列放在已经具有现有数据(例如，将函数/表达式(例如，强制类型转换)应用于它的数据)上的现有数据(例如，“ABXY”，“X1”，“ETC”)的表上，您的RDBMS将不允许您创建计算列。如果表中没有数据，它将允许您放置PERSISTED选项，但是随后当您尝试插入引发异常的数据(例如 insert into tbl(col) values('ABXY'))时，RDBMS将不允许您保存数据。因此，仅数字文本可以保存在您的表中，您的PERSISTED计算列退化为约束检查，尽管它完全绕了弯。

作为参考，这是持久化的计算列示例:

ALTER TABLE tbl ADD
numeric_equivalent  AS cast(col as int) persisted

现在，我们当中有些人可能会想不将PERSISTED选项放在计算列上。就性能目的而言，这将是一种失败的尝试，因为您稍后可能无法在它们上创建索引。稍后，当您想在未持久的计算列上创建索引，并且该表已经具有数据“ABXY”时，数据库将不允许您创建索引。索引创建需要从列中获取值，并且如果该列引发异常，则不允许您在该列上创建索引。

如果我们尝试作弊，即在创建表后立即在该非持久计算列上创建索引，则数据库将允许您这样做。但是，当我们稍后在表中插入“ABXY”时，将不会保存该索引，在将数据插入表后，数据库会自动构造索引。索引构造函数接收异常而不是数据，因此它无法为我们尝试插入的数据创建索引条目，随后将不会插入数据。

那么如何在计算列上获得索引必杀技？首先，我们确保计算列是“PERSISTED”，这样做将确保立即出现错误；如果我们不使用PERSISTED选项，那么任何可能引发异常的事件都将被推迟到索引的构造中，只是使事情以后失败。错误较早发生时更容易发现。使列保持不变后，在其上放置一个索引

因此，如果我们有现有数据“00”，“01”，“2”，这将使我们能够生成持久化的计算列。现在，在此之后，如果我们插入“ABXY”，它将不会被插入，数据库将无法持久化计算列中引发异常的任何内容。因此，我们将只滚动不会引发异常的自己的演员表。

机智(只需将其翻译成Oracle等效文件):

create function cast_as_int(@n varchar(20)) returns int with schemabinding
begin

    begin try
       return cast(@n as int);
    end try
    begin catch 
       return null;
    end catch

end;

请注意，在UDF中捕获异常在Sql Server中尚不起作用，但是Microsoft have plans to support that

现在，这是我们的非异常引发的持久计算列:

ALTER TABLE tbl ADD
numeric_equivalent  AS cast_as_int(a) persisted

删除现有索引，然后重新创建它:

create index ix_num_equiv on tbl(numeric_equivalent)    

现在，此查询将成为索引中标的公民，性能优异，并且即使条件的顺序相反，也不会引发异常:

select * from tbl where numeric_equivalent = 0 and is_col_numeric = 1

为了提高性能，由于numeric_equivalent列不再引发任何异常，因此对is_col_numeric不再使用，因此只需使用以下代码:

select * from tbl where numeric_equivalent = 0