本帖仅讨论静态SQL. 阅读对象: Oracle 初中级用户.
SQL书写的一般技巧就在于SQL查询中的数据构造,首先你必须知道的静态SQL的一些特性:
a) 一旦书写完一个静态SQL, 那么输出的列数, 列类型就已经确定, 不会在SQL执行过程中动态增加列数或改变列类型.
b) SQL查询本身是对表中已有数据进行查询, 因此你不能通过SQL获取表中不存在的数据.
c) SQL查询不允许递归, 也就是不允许在SQL查询的结果上重复的执行SQL查询.
对于这些特性中的c),我们也容易理解, 毕竟SQL是结构化数据查询语言, 目的就是查询数据, 所以不具备 PLSQL代码的循环, 递归等功能是再正常不过的事情.比如我有数据a, 经
过一逻辑运算后得到b, 然后我对b再采用同样的计算逻辑得到c, 以此类推, 知道最后的结果满足某条件, 这样的递归计算逻辑SQL就是无法实现的. 而对于b)特性稍微想想,我们也
可以理解, 你不能指望SQL查询出不存在的数据出来, 比如盒子里有个苹果, 你总不能多拿出一个梨子来吧.
SQL查询的主要运用除了直接查询出表中的数据之外就是对于已知数据进行加工得到另一种形式的数据. 这里边包含两种情况, 第一, 查询列的构造. 比如仓库里谷物, 那么我进行
加工得到米和糠, 这个米和糠不是凭空产生, 它是从已有物品谷物加工而来. 这是SQL能做的.
比如 scott 下的emp 表为例, 其中有 雇用日期 hiredate, 则我们可以得到员工的生日, 年龄列表, 如下:
select empno,ename,trunc((sysdate - hiredate)/365) age, to_char(hiredate,'mm/dd') birthday from emp;
EMPNO ENAME
AGE BIRTHDAY
----- ---------- ---------- --------
7369 SMITH
27 12/17
7499 ALLEN
27 02/20
7521 WARD
27 02/22
........
第二, 查询行的构造, 这种情况的构造要稍微难一些, 比如我要对对公司员工人数按照部门做个统计, 那么各个部门的人数表就不是已知数据, 而是通过员工信息表进行汇总会得
到构造行.
比如我们要得到部门的人数统计还有总人数, 如下:
select deptno,count(*) from emp group by rollup(deptno);
DEPTNO COUNT(*)
------ ----------
10
3
20
5
30
6
14
最后一行为总人数.
有时,我们需要输出一个列表, 但是这个列表中的数据并不是从现成的数据表中获取的, 而可能是通过一定的计算逻辑得到的, 那么这个时候的数据构造和前面的数据构造又有些不
同.比如我要得到某个月的日期列表, 当我们已知月份的时候, 我们就可以推算出该月的日期列表, 根本不需要将日期列表存放于数据表中. 那么既然不存在于数据表中, 我们要如
何来查询出这个列表呢? 首先我们必须要得到一个具有该月份天数的数据行数, 当我们得到数据行过后, 在通过某种计算逻辑构造日期列, 最终就得到了该月份的日期列表. 要得
到数据行, 我们可以查询任意包含数据的表来获取数据行, 只是我们不需要该数据表中的任何列信息, 我们所要的就是重新构造一个日期列. 但这里有个限制就是, 如果表中只有
20条数据, 我将无法得到30个数据行(假定该月份有30天). 这时你有两种选择, 一是查询具有更多数据行的表, 并且限制返回行数为你需要的行数, 其二是进行表的自身连接, 形
成笛卡尔积, 这样 20*20 最多就可以返回 400 行, 你只需要限制返回30行即可. 当然高版本Oracle还可以用分层查询(select level from dual connect by level 2,同时假定 all_objects 表中的数据行数大于 N.
select mod(n,rownum + 1) mod_n from all_objects where rownum <= n - 2;
这一步构造了 N- 2 行数据, 对于每一行都用一个自然数去试除 N, 并得到一个余数.
接下来, 就是在这个已知的结果集上再次执行SQL查询, 判断其中是否存在有 能整除的 行, 余数为0就是能整除.
当然这最后一步有多种判断法, 我们这里使用上述结果集中能整除的行数超过1则是合数, 否则是素数的方法来判定.
select decode(count(*),0,'素数','合数') from
(select mod(n,rownum + 1) mod_n from all_objects where rownum <= n - 2)
where mod_n = 0;
补充:构造数据行, 除了从已知表中的数据得到, 分层查询得到, 还可以从有下面这种方式,不知道这个是不是标准SQL中有的, 只是这种方式的行中没有行号信息, 需要再套一层获取行号信息.
select 1 from dual group by cube(2,2,2);
select rownum from (select 1 from dual group by cube(2,2,2));
这里 group by cube(2,2,2) 可以得到 2*2*2 个数据行, group by cube(2,2,2,2) 就可以得到 16 个数据行.
原帖由 homeworld80 于 2008-10-27 18:03 发表
楼主的构造数据行的方式较不灵活，
这里 group by cube(2,2,2) 可以得到 2*2*2 个数据行, group by cube(2,2,2,2) 就可以得到 16 个数据行，这句话容易误导读者，
得到的数据行和 group by cube(2,2,2)里面的2没有关系，完全可以改成 group by cube(1,1,1)，这其实是一个组合问题，计算如下：
C(0,n)+...+C(n,n)
这个式子不知道该怎么简化了，呵呵。 原帖由 newkid 于 2008-10-27 22:06 发表 简化就是2^n
这里原本我是想用 group by cube(null,null,null) 等形式的, 后来为了方便记忆最终得到的行数而使用了常数2, 的确会导致误解, 多谢提醒. 使用 cube 是对数据进行交叉统计, 这个知识点不在这个讨论范围.
[ 本帖最后由 N先生 于 2008-10-28 09:55 编辑 ]
|
