SQL里面有乐子

通过本文可以了解：

1、Oracle的With应用场景；

2、Connect by的简单示例；

3、Dual表与rownum的威力;

4、Oracle中的隐式数据类型转换；

5、SQL性能的优化简单策略；

6、关键有的例子挺有乐子哦；

SQL语言主要操作增删改，但是内部机理却是非常复杂。在使用SQL语言的发烧友里，往往会有一句SQL解决问题的要求。当然这样SQL往往会比较复杂，也会造成可读性不好。但从SQL语言这种“丰富”的表情里面搜索出一些非常有意义的玩法，还是非常有乐子的。下面我们就以传说中的某面试题开始：

小小+霸霸+王王=小霸王

已知 小小+霸霸+王王=小霸王

小=?,霸=?,王=?

用sql求证

这道题正向思考时，就会想到需要三重循环，也就是说要有三表关联来实现。只是这三张表可以自关联。公司部门里进行了一次有“可乐”奖品的测试，结果出现的结果真的是各放异彩。当然，最通用的方法是（下文的SQL都是为了紧凑而尽量集结在一起的）：

create table n_list (n int); --n为0-9

select a.n, b.n, c.n from n_list a, n_list b, n_list c

 where 11 * (a.n + b.n + c.n) = a.n * 100 + b.n * 10 + c.n;

这样得到结果是1,9,8和0,0,0。实际上n为0时意义不大可以忽略，使用Oracle的一般写法，也可以利用dual表和connect by来实现，即n_list表可以写成类似结果：

Select rownum-1 n from dual connect by rownum<11;

只要把它套入到SQL中来替代n_list即可，但这样SQL还比较长。Oracle从9i开始，还有可以利用With的写法,那么最终最简洁而不借助其它数据库表的写法应该是:

with n_list as

( select rownum-1 n from dual connect by rownum<11 )

 select a.n, b.n, c.n

   from n_list a, n_list b, n_list c

  where 11 * (a.n + b.n + c.n) = a.n * 100 + b.n * 10 + c.n;

由于上述算法是从左到右，使用三表才实现，不用说开销实际上不是最小。如果反向思考这个问题只要一个循环就能解决。即把每一个整数进行分拆成个十百位，看它的计算结果是否符合要求即可，它的语句实现是这样的：

with n_list as ( select rownum n from dual connect by rownum<1000 )

select n from n_list where (substr(n, 1, 1) + substr(n, 2, 1) + substr(n, 3, 1)) * 11 = n and n > 100;

其实这里用到Oracle的隐性数据转换，n是数值，通过substr变成字符，再通过计算时又变回数字。由于Oracle不对数据类型进行强检查，所以常会发生某个字符串列进行计算时发生错误的情况，这也是使用Oracle写SQL时要注意的问题。我们可以对比两种写法执行计划之间差异：

 

单表与三表之间的差距巨大。现在再来一个类似的趣题吧：

as * a = Man

每个字母代表数字0-9之间的数字，不同字母代表不同的数字

这请问A是几？

这个题和上面的题有相类似之处，具体不需研究，只要给出SQL来解决问题即可，很显然，a的值肯定不是0，也不是1，当然在求证时不用细考虑。可以采用这样的SQL来完成：

with n_list as ( select a.n na, b.n ns, (a.n * 10 + b.n) * a.n man

    from (select rownum n from dual connect by rownum<10) a,

    (select rownum n from dual connect by rownum<10) b where a.n <> b.n )

select na, ns, man  from n_list where substr(man, 2, 1) = na and na <> ns

   and substr(man, 1, 1) not in ( na, ns, substr(man,3,1)) and substr(man, 3, 1) not in ( na, ns );

此SQL的关键点在于各不相同会比较麻烦一些，即要na与ns,man中的第1位和第3位不相同，也要考虑到man第3位与其它值也不相同，在with n_list中我们已经排除了as中的s与a的相同部分。这里同样有数据类型隐式转换，所以才会看起来比较简洁。

 

适“可”而止

这个要求可能有点无理头，如下的数据：

1001 徐兢 1003

1002 徐可可

1003 程大山 

分别代码职工工号(zhigonggh)、姓名(xingming)和图章号(tuzhanghao)，这个图章号是医院里面代表医生的唯一标识（不是所有职工都有），由于用类似于以下的语句：

Select * from zhigongid where zhigonggh='@01' or tuzhanghao='@01';

作为标准方案，当输入重要条件是@01=1003时就会有多重数据的返回，要求这样的结果：

1、如果所输入在图章号中能唯一对应（它是不会重复的），则返回它所对应的职工；

2、如果在图章号未匹配到，则返回zhigonggh所对应的职工。

我们希望它能够“适可而止”，即能根据需要只返回一条数据，这在界面层本来可以分两次完成，或在后台通过存储过程，但一条SQL是皆大欢喜的。其基本思路是：

1、需要用union;

2、需要对tuzhanghao条件的数据进行计数；

3、需要把结果集作为条件。

首先select count(*) from gy_zgxx where tuzhanghao ='@01'这个条件是肯定要的；那么如何实现一个条件有数据，另外一个条件为空呢？这里就容易让我们联想到decode，in和空串，也就是说当统计出来的SQL是一条数据时，我们让它返回空串，把空串放入到in中，自然就不起作用了，所以可以这样写结果集：

select decode(b.rn,1,'',a.zhigonggh )

from gy_zgxx a, ( select count(*) rn from gy_zgxx where tuzhanghao='1003' ) b

where a.zhigonggh='1003;

当有图章号时，返回都是空串，否则就是相应的zhigonggh，把它和图章号的条件合并后就是：

with gy_zgxx as( select '1001' as zhigonggh, '徐兢' as xingming, '1003' as tuzhanghao from dual

union all select '1002', '徐可可', '' from dual union all select '1003', '程大山', '' from dual )

Select zhigonggh from gy_zgxx where zhigonggh in (

select zhigonggh from gy_zgxx  where tuzhanghao='@01'

Union select decode(b.rn, 1, '', a.zhigonggh)

from gy_zgxx a, (select count(*) rn from gy_zgxx where tuzhanghao = '@01') b

where a.zhigonggh = '@01' );

使用时把不同的条件套入到@01。此SQL解决了输入唯一的图章号时只返回符号要求的一条数据。

 

列转行不用union的实现方法

一个排班的数据，因为历史遗留的原因，在数据中保留了上午、下午、晚上的三列数据，但是实际使用时需要按照每一个时间段一行数据结果。所以就需要把三个列转换成三行数据，通常写法是这样的：

with n_paiban as (

select '1001' zhigongid, 12 shangwuxh, 20 xiawuxh, 5 wanshangxh from dual

union all select '1002', 10, 10, 6 from dual union all

select '1003', 20, 20, 5 from dual )

Select Zhigongid, '上午', Shangwuxh From n_Paiban Union All

Select Zhigongid, '下午', Xiawuxh From n_Paiban Union All

Select Zhigongid, '晚上', Wanshangxh From n_Paiban;

这样就会带来一个问题每一次union all就是一次遍历。当用了更多的union al时开销就会更大。而排班表又可能会与多表关联，最终导致查询的性能非常低下。由于表关联的迪卡尔特性和decode函数的分支作用，我们可以做一张几个纪录的伪表与它进行关联，即可以写成类似于这样的语句：

select zhigongid, decode(rn, 1, '上午', 2, '下午', 3, '晚上') shijian,

       decode(rn, 1, shangwuxh, 2, xiawuxh, 3, wanshangxh) xianhaoshu

  from n_paiban a, (select rownum rn from dual connect by rownum<4 );

这样表n_paiban进行过一次简单关联后，实际上只进行一次扫描即可完成。性能基本上提高三倍，自然和union all的个数有关，当越多时性能提高越多，它的可读性可能没有原来强，只要是理解它，其实也没有什么难度。所以达到同样的目的，更好地解决问题的SQL其实还是很有乐子的。