Database_拾遗

1.rownum的原理与用法

rownum是一个逻辑概念，是附加到查询结果上的一个序号，即先有查询结果才会有rownum

假如表TB_TEST1有20条数据

select * from TB_TEST1 where rownum <= 10;

此时可以查询到10条数据，但是

select * from TB_TEST1 WHERE  rownum > 10;

的查询结果为空

因为查询结果是读出一条就为这一条数据附加一个rownum=1，当不满足rownum > 10的条件便丢弃此条数据；由于之前的数据被丢弃所以查询过程中查询结果为空，查询出的下一条数据又会作为新的首条数据同样被附加rownum=1，也同样因不满足条件而被丢弃。最终导致查询结果没有数据

 

如果想查询到10条之后的数据，可以把原本的sql语句作为子查询在其外层套一层查询语句

select col1, col2, … from 
(
    select rownum as rn, col1, col2, …  from TB_TEST1
)
where rn > 10;

 

 

2.Oracle数据库in函数的限制

Oracle数据库in函数中参数个数限制为1000

假如目前有两张表TB_TEST1, TB_TEST2均有ID字段，想得到TB_TEST1中存在但在TB_TEST2中不存在的ID，通常会写成

select id from TB_TEST1
where id not in (
    select id from TB_TEST2
);

如果TB_TEST2的数据量超过1000为1500的话，这条sql语句是无法得到预期结果的，应改为

select id from TB_TEST1
where id not in (
    select id from TB_TEST2
    where rownum <=900
)
and id not in (
    select id from (
        select rownum as rn, id from TB_TEST2
    )
    where rn > 900
);

 

3.空间换时间

select id from TB_TEST1
where id not in (
    select id from TB_TEST2
    union
    select id from TB_TEST3
    union 
    select id from TB_TEST4
    where rownum <=900
)
and id not in (
    select id from (
        select id from TB_TEST2
        union
        select id from TB_TEST3
        union 
        select id from TB_TEST4
    )
    where rn > 900
    and rn <=1800
)
and id not in (
    select id from (
        select id from TB_TEST2
        union
        select id from TB_TEST3
        union 
        select id from TB_TEST4
    )
    where rn > 1800
)

其中TB_TEST2、TB_TEST3、TB_TEST4的联表查询被执行多次

不如新建一张临时表将联表查询结果存进去，此后的查询都只在临时表里取

假设临时表名为TB_TEST_TMP，先将联表查询结果复制到临时表中

insert into TB_TEST_TMP (id) 
select id from (
    select id from TB_TEST2
    union
    select id from TB_TEST3
    union 
    select id from TB_TEST4
    )
)

然后之前作查询的语句更新

select id from TB_TEST1
where id not in (
    select id from TB_TEST_TMP
    where rownum <=900
)
and id not in (
    select id from (
        select rownum as rn, id from TB_TEST_TMP
    )
    where rn > 900
    and rn <=1800
)
and id not in (
    select id from (
        select rownum as rn, id from TB_TEST_TMP
    )
    where rn > 1800
)