主页 / dba / 问题 / 114366
Accepted
Bill N. Varelli
Asked: 2015-09-08 06:09:24 +0800 CST

选择不在的问题

  • 772

我想显示介于 0 和 10 之间但不存在于当前表中的值

例如 ：

场地
1个
2个
3个
5个
9
8个

预期结果：

7
4个

问题是该列field是 a varchar，所以我首先转换为数字然后进行验证

SELECT TO_NUMBER(yy.ent_sub_dealer_code,'9') FROM a_entidades yy 
where TO_NUMBER(yy.ent_sub_dealer_code,'9') not in (SELECT LEVEL n
                                                   FROM DUAL
                                                   CONNECT BY LEVEL <= 9     )

有什么建议么？

oracle subquery

1 个回答

  1. Best Answer
    create table t1 (x varchar2(2));

insert into t1 (x) values ('1');
insert into t1 (x) values ('2');
insert into t1 (x) values ('3');
insert into t1 (x) values ('5');
insert into t1 (x) values ('6');
insert into t1 (x) values ('8');
insert into t1 (x) values ('9');
insert into t1 (x) values ('10');
insert into t1 (x) values ('11');
commit;

SQL>  select level as x
  2     from dual
  3  connect by level <= 9
  4    minus
  5   select to_number(x)
  6     from t1;

         X
----------
         4
         7

2 rows selected.
    • 2

相关问题