单条SQL语句实现复杂逻辑几例~~

junsansi · 发表于 2008-4-14 10:55

1、按指定规则生成指定商品2002年销售额，其中彩电项值为2001年的销售额加2000年的销售额，微波炉为2000年销售额, 然后汇总彩电+微波炉的2002年总销售额。

表数据如下：
CITY  PRODUCT YEAR  SALES
------  -------------- --------  ---------
北京彩电 1999 3000
北京彩电 2000 2500
北京彩电 2001 4500
北京微波炉 1999 800
北京微波炉 2000 7000
北京微波炉 2001 333
北京冰箱 1999 2323
北京冰箱 2000 1212
北京冰箱 2001 7676
天津彩电 1999 212121
天津彩电 2000 434343
天津彩电 2001 564566
天津微波炉 1999 23432
天津微波炉 2000 232
天津微波炉 2001 34234
天津冰箱 1999 324324
天津冰箱 2000 8987686
天津冰箱 2001 768678

要求用SQL实现如下效果：
CITY  PRODUCT YEAR  SALES
------  -------------- --------  ---------
天津微波炉 1999 23432
天津微波炉 2000 232
天津微波炉 2001 34234
天津微波炉 2002 232
天津冰箱 1999 324324
天津冰箱 2000 8987686
天津冰箱 2001 768678
天津彩电 1999 212121
天津彩电 2000 434343
天津彩电 2001 564566
天津彩电 2002 998909
天津彩电+微波炉 2002 999141
北京微波炉 1999 800
北京微波炉 2000 7000
北京微波炉 2001 333
北京微波炉 2002 7000
北京冰箱 1999 2323
北京冰箱 2000 1212
北京冰箱 2001 7676
北京彩电 1999 3000
北京彩电 2000 2500
北京彩电 2001 4500
北京彩电 2002 7000
北京彩电+微波炉 2002 14000

建表语句如下：
create table tmp1(CITY varchar2(20), PRODUCT varchar2(20), YEAR number,  SALES number);
insert into tmp1 values ('北京','彩电', 1999, 3000);
insert into tmp1 values ('北京','彩电', 2000, 2500);
insert into tmp1 values ('北京','彩电', 2001, 4500);
insert into tmp1 values ('北京','微波炉', 1999, 800);
insert into tmp1 values ('北京','微波炉', 2000, 7000);
insert into tmp1 values ('北京','微波炉', 2001, 333);
insert into tmp1 values ('北京','冰箱', 1999, 2323);
insert into tmp1 values ('北京','冰箱', 2000, 1212);
insert into tmp1 values ('北京','冰箱', 2001, 7676);
insert into tmp1 values ('天津','彩电', 1999, 212121);
insert into tmp1 values ('天津','彩电', 2000, 434343);
insert into tmp1 values ('天津','彩电', 2001, 564566);
insert into tmp1 values ('天津','微波炉', 1999, 23432);
insert into tmp1 values ('天津','微波炉', 2000, 232);
insert into tmp1 values ('天津','微波炉', 2001, 34234);
insert into tmp1 values ('天津','冰箱', 1999, 324324);
insert into tmp1 values ('天津','冰箱', 2000, 8987686);
insert into tmp1 values ('天津','冰箱', 2001, 768678);

解题思路：
本题初看起来一般都会下意识选择通过group by rollup子句生成，但如果你选择直接通过group by rollup的方式：
[php]
JSSWEB> select city, product, year, sum(sales)
   2 from tmp1
   3 group by city, rollup(product, year)
   4  ;

CITY                PRODUCT                   YEAR SUM(SALES)
-------------------- -------------------- ---------- ----------
北京                冰箱                      2000    1212
北京                冰箱                      1999    2323
北京                冰箱                      2001    7676
北京                冰箱                               11211
北京                彩电                      2000    2500
北京                彩电                      1999    3000
北京                彩电                      2001    4500
北京                彩电                               10000
北京                微波炉                   2000    7000
北京                微波炉                   1999       800
北京                微波炉                   2001       333
北京                微波炉                               8133
北京                                                    29344
天津                冰箱                      2000 8987686
天津                冰箱                      1999    324324
天津                冰箱                      2001    768678
天津                冰箱                            10080688
天津                彩电                      2000    434343
天津                彩电                      1999    212121
天津                彩电                      2001    564566
天津                彩电                            1211030
天津                微波炉                   2000       232
天津                微波炉                   1999    23432
天津                微波炉                   2001    34234
天津                微波炉                            57898
天津                                                 11349616

26 rows selected
--
[/php]
这跟预想的结果有不小差距。

这是因为要求的数据是涉及逻辑运算的，比如某些数据需要2001年相加，某些数据由2000+2001汇总，因此我们在group by之前需要先按照条件对sales做些处理，彩电要取2000年和2001年的，而微波炉要取2000年的。

[php]
JSSWEB> select city,
   2       product,
   3       year,
   4       sales,
   5       decode(product,
   6             '彩电',
   7             decode(year, 2000, sales, 2001, sales, 0),
   8             '微波炉',
   9             decode(year, 2000, sales, 0)) ns
10 from tmp1;

CITY                PRODUCT                   YEAR    SALES       NS
-------------------- -------------------- ---------- ---------- ----------
北京                彩电                      1999    3000       0
北京                彩电                      2000    2500    2500
北京                彩电                      2001    4500    4500
北京                微波炉                   1999       800       0
北京                微波炉                   2000    7000    7000
北京                微波炉                   2001       333       0
北京                冰箱                      1999    2323
北京                冰箱                      2000    1212
北京                冰箱                      2001    7676
天津                彩电                      1999    212121       0
天津                彩电                      2000    434343    434343
天津                彩电                      2001    564566    564566
天津                微波炉                   1999    23432       0
天津                微波炉                   2000       232       232
天津                微波炉                   2001    34234       0
天津                冰箱                      1999    324324
天津                冰箱                      2000 8987686
天津                冰箱                      2001    768678

18 rows selected
--
[/php]

然后在做sum的时候，我们只需要判断一下，比如产品为彩电或微波炉的话，sum(ns)，否则sum(sales)，最后按照城市产品和年份排下序即可：
[php]
JSSWEB> select city,
   2       nvl(product, '彩电+微波炉') product,
   3       nvl(year, 2002) year,
   4       decode(product,
   5             '彩电',
   6             nvl2(year, sum(sales), sum(ns)),
   7             '微波炉',
   8             nvl2(year, sum(sales), sum(ns)),
   9             null,
10             sum(ns),
11             sum(sales)) sales
12 from (select city,
13                product,
14                year,
15                sales,
16                decode(product,
17                      '彩电',
18                      decode(year, 2000, sales, 2001, sales, 0),
19                      '微波炉',
20                      decode(year, 2000, sales, 0)) ns
21          from tmp1) a
22 group by city, rollup(product, year)
23  having sum(ns) is not null or year is not null
24 order by 1, 2, 3;

CITY                PRODUCT                   YEAR    SALES
-------------------- -------------------- ---------- ----------
北京                冰箱                      1999    2323
北京                冰箱                      2000    1212
北京                冰箱                      2001    7676
北京                彩电                      1999    3000
北京                彩电                      2000    2500
北京                彩电                      2001    4500
北京                彩电                      2002    7000
北京                彩电+微波炉             2002    14000
北京                微波炉                   1999       800
北京                微波炉                   2000    7000
北京                微波炉                   2001       333
北京                微波炉                   2002    7000
天津                冰箱                      1999    324324
天津                冰箱                      2000 8987686
天津                冰箱                      2001    768678
天津                彩电                      1999    212121
天津                彩电                      2000    434343
天津                彩电                      2001    564566
天津                彩电                      2002    998909
天津                彩电+微波炉             2002    999141
天津                微波炉                   1999    23432
天津                微波炉                   2000       232
天津                微波炉                   2001    34234
天津                微波炉                   2002       232

24 rows selected
--
[/php]

上述实现从技术角度看并不太难，所使用的函数、语法也都比较常见，只是由于业务需求涉及了一些逻辑，因此在实现的时候不少代码都用在了逻辑判断上，这样就造成了代码过长，同时也降低了可读性。

下面再介绍一种更简单也更高效的实现方式，借助model和partition，可以直接指定计算规则：
[php]
JSSWEB> select * from TMP1
   2  MODEL
   3  PARTITION BY (city) DIMENSION BY (product, year)
   4  MEASURES (sales sal)
   5  RULES
   6  (sal['彩电', 2002] = sal['彩电', 2001] + sal['彩电', 2000],
   7  sal['微波炉', 2002] = sal['微波炉', 2000],
   8  sal['彩电+微波炉', 2002] = (sal['彩电',2002]+sal['微波炉',2002]))
   9  ORDER BY 1,2,3
10  ;

CITY                PRODUCT                   YEAR       SAL
-------------------- -------------------- ---------- ----------
北京                冰箱                      1999    2323
北京                冰箱                      2000    1212
北京                冰箱                      2001    7676
北京                彩电                      1999    3000
北京                彩电                      2000    2500
北京                彩电                      2001    4500
北京                彩电                      2002    7000
北京                彩电+微波炉             2002    14000
北京                微波炉                   1999       800
北京                微波炉                   2000    7000
北京                微波炉                   2001       333
北京                微波炉                   2002    7000
天津                冰箱                      1999    324324
天津                冰箱                      2000 8987686
天津                冰箱                      2001    768678
天津                彩电                      1999    212121
天津                彩电                      2000    434343
天津                彩电                      2001    564566
天津                彩电                      2002    998909
天津                彩电+微波炉             2002    999141
天津                微波炉                   1999    23432
天津                微波炉                   2000       232
天津                微波炉                   2001    34234
天津                微波炉                   2002       232

24 rows selected
[/php]
*更多关于model子句的语法可以参考：
http://download.oracle.com/docs/ ... _10002.htm#i2172805

junsansi · 发表于 2008-4-14 10:56

2、查询字段a的值连续三条以上相同的记录

初始表数据如下：
a b c
- - -
1 2 3
1 4 5
1 3 6
2 3 3
1 5 7
2 5 8
1 6 9
1 2 3
1 4 5
1 3 6

要求用SQL实现如下效果：
a b c
- - -
1 2 3
1 4 5
1 3 6
1 6 9
1 2 3
1 4 5
1 3 6

建表语句如下：
create table tmp2 (a number,b number, c number);
insert into tmp2 values (1,2,3);
insert into tmp2 values (1,4,5);
insert into tmp2 values (1,3,6);
insert into tmp2 values (2,3,3);
insert into tmp2 values (1,5,7);
insert into tmp2 values (2,5,8);
insert into tmp2 values (1,6,9);
insert into tmp2 values (1,2,3);
insert into tmp2 values (1,4,5);
insert into tmp2 values (1,3,6);
commit;

解题思路：
这道题看起来非常简单，我们甚至一眼就能看出来哪些记录是连接3条相同的，但千万不要被其简单的表象迷惑了，特别是那些下意识就能得出结论的问题，这往往会让我们的思维陷入到自我的思维误区中，而不再以计算机的执行模式去理解问题，因此这题核心要解决的问题就是将我们的思维方式转换成sql可以理解的记数方式。
先来理一理我们的逻辑，看看能否转换成对应的SQL操作：

首先肯定是拿上一条与下一条做对比，看看是否相同--->lead,lag分析函数可以实现这一点
计算相同数--->count分析函数可以实现，但是这里面有一个问题，分析函数虽然是逐条对比生成结果，但此处我们的依据是是否相同的字段值，假设该字段值为0或1的话，count() over(partition by )就没有了依照，因此我们需要先将比较的结果字段通过sum() over(order by rownum)计算相加，以便生成分区用的字段。
如果计数>3则这些记录符合我们的需求

OK，思路理清了，下面一步步来试试，首先生成比较是否相同的字段：
[php]
JSSWEB> select a.*,
   2       rownum rn,
   3       decode(a, lag(a, 1, a) over(order by rownum), 0, 1) na
   4 from tmp2 a
   5  ;

      A       B       C       RN       NA
---------- ---------- ---------- ---------- ----------
      1       2       3       1       0
      1       4       5       2       0
      1       3       6       3       0
      2       3       3       4       1
      1       5       7       5       1
      2       5       8       6       1
      1       6       9       7       1
      1       2       3       8       0
      1       4       5       9       0
      1       3       6       10       0

10 rows selected
--
[/php]
*rownum列是为了排序用

然后生成用于partition的列
[php]
JSSWEB> select b.*, sum(na) over(order by rn) so
   2 from (select a.*,
   3                rownum rn,
   4                decode(a, lag(a, 1, a) over(order by rownum), 0, 1) na
   5          from tmp2 a) b
   6  ;

      A       B       C       RN       NA       SO
---------- ---------- ---------- ---------- ---------- ----------
      1       2       3       1       0       0
      1       4       5       2       0       0
      1       3       6       3       0       0
      2       3       3       4       1       1
      1       5       7       5       1       2
      2       5       8       6       1       3
      1       6       9       7       1       4
      1       2       3       8       0       4
      1       4       5       9       0       4
      1       3       6       10       0       4

10 rows selected

--
[/php]

这下就清晰多了，剩下的就没难度了，count() over()生成数量，取数量大于2的记录即可：
[php]
JSSWEB> select a,b,c from(
   2  select c.*, count(so) over(partition by so) ct
   3 from (select b.*, sum(na) over(order by rn) so
   4          from (select a.*,
   5                      rownum rn,
   6                      decode(a, lag(a, 1, a) over(order by rownum), 0, 1) na
   7                   from tmp2 a) b) c
   8  )where ct>=3
   9  ;

      A       B       C
---------- ---------- ----------
      1       2       3
      1       4       5
      1       3       6
      1       6       9
      1       2       3
      1       4       5
      1       3       6

7 rows selected
--
[/php]

junsansi · 发表于 2008-4-14 10:58

3、查询员工ID：1000的实际工作月数，注意过滤兼职月份

记录集如下：
ID STATION START_DATE END_DATE
------ ----------- ------------------- ----------------
1000 开发 2000-01-01 2000-04-01
1000 测试 2000-07-01 2000-10-01
1000 副经理 2001-01-01 2001-04-01
1000 DBA 2000-02-01 2000-03-01
1000 兼职经理 2000-03-01 2000-08-01
1000 经理 2001-05-01 2001-08-01

该员工的实际工作月份应为：15

建表语句如下：
create table tmp3 (id number,station varchar2(20),start_date date,end_date date);
insert into tmp3 (ID, STATION, START_DATE, END_DATE)
values ('1000', '开发', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-04-2000', 'dd-mm-yyyy'));
insert into tmp3 (ID, STATION, START_DATE, END_DATE)
values ('1000', '测试', to_date('01-07-2000', 'dd-mm-yyyy'), to_date('01-10-2000', 'dd-mm-yyyy'));
insert into tmp3 (ID, STATION, START_DATE, END_DATE)
values ('1000', '副经理', to_date('01-01-2001', 'dd-mm-yyyy'), to_date('01-04-2001', 'dd-mm-yyyy'));
insert into tmp3 (ID, STATION, START_DATE, END_DATE)
values ('1000', 'DBA', to_date('01-02-2000', 'dd-mm-yyyy'), to_date('01-03-2000', 'dd-mm-yyyy'));
insert into tmp3 (ID, STATION, START_DATE, END_DATE)
values ('1000', '兼职经理', to_date('01-03-2000', 'dd-mm-yyyy'), to_date('01-08-2000', 'dd-mm-yyyy'));
insert into tmp3 (ID, STATION, START_DATE, END_DATE)
values ('1000', '经理', to_date('01-05-2001', 'dd-mm-yyyy'), to_date('01-08-2001', 'dd-mm-yyyy'));
commit;

解题思路：
这道题核心的问题在于在岗时间可能存在兼职的情况，因此要求实际工作月份的话就不能单纯sum(end_date-start_date)，如果说你一心想着比较各条记录的start_date,end_date，判断是否存在兼职月的话，黑黑，我不是说这样实现不了，只是。。。。太复杂了！！不妨换一种思路，我们只要遍历出它所有工作过的月份，然后count(distinct date)就是实际工作月份了。
要求出所有工作过的月份，就必须首先构造出足够数据的记录出来，这并不困难，熟悉connect by的朋友一定不陌生这种写法：select level from dual connect by rownum<=n;这里我们也借助这种方式来构造指定数据的记录集：
[php]
JSSWEB> select level - 1 lv
   2 from dual
   3  connect by rownum <=
   4          (select max(Months_between(end_date, start_date)) mb from test)
   5  ;

      LV
----------
      0
      1
      2
      3
      4
--
[/php]
*Level为什么要-1呢，因为我们准备用add_months函数来生成日期，起始月数量当然得是0啊

然后与tmp3表做Cartesian，即能够生成比我们希望数量还要多的结果集(多不怕，别少就行啊)：
[php]
JSSWEB> select a.*,
   2       Months_between(end_date, start_date) mb,
   3       b.lv,
   4       add_months(a.start_date, lv) am
   5 from tmp3 a,
   6       (select level - 1 lv
   7          from dual
   8       connect by rownum <= (select max(Months_between(end_date, start_date)) mb
   9                               from test t)) b
10  ;

      ID STATION             START_DATE  END_DATE          MB       LV AM
---------- -------------------- ----------- ----------- ---------- ---------- -----------
   1000 开发                2000-01-01  2000-04-01          3       0 2000-01-01
   1000 测试                2000-07-01  2000-10-01          3       0 2000-07-01
   1000 副经理             2001-01-01  2001-04-01          3       0 2001-01-01
   1000 DBA                2000-02-01  2000-03-01          1       0 2000-02-01
   1000 兼职经理          2000-03-01  2000-08-01          5       0 2000-03-01
   1000 经理                2001-05-01  2001-08-01          3       0 2001-05-01
   1000 开发                2000-01-01  2000-04-01          3       1 2000-02-01
   1000 测试                2000-07-01  2000-10-01          3       1 2000-08-01
   1000 副经理             2001-01-01  2001-04-01          3       1 2001-02-01
   1000 DBA                2000-02-01  2000-03-01          1       1 2000-03-01
   1000 兼职经理          2000-03-01  2000-08-01          5       1 2000-04-01
   1000 经理                2001-05-01  2001-08-01          3       1 2001-06-01
   1000 开发                2000-01-01  2000-04-01          3       2 2000-03-01
   1000 测试                2000-07-01  2000-10-01          3       2 2000-09-01
   1000 副经理             2001-01-01  2001-04-01          3       2 2001-03-01
   1000 DBA                2000-02-01  2000-03-01          1       2 2000-04-01
   1000 兼职经理          2000-03-01  2000-08-01          5       2 2000-05-01
   1000 经理                2001-05-01  2001-08-01          3       2 2001-07-01
   1000 开发                2000-01-01  2000-04-01          3       3 2000-04-01
   1000 测试                2000-07-01  2000-10-01          3       3 2000-10-01
   1000 副经理             2001-01-01  2001-04-01          3       3 2001-04-01
   1000 DBA                2000-02-01  2000-03-01          1       3 2000-05-01
   1000 兼职经理          2000-03-01  2000-08-01          5       3 2000-06-01
   1000 经理                2001-05-01  2001-08-01          3       3 2001-08-01
   1000 开发                2000-01-01  2000-04-01          3       4 2000-05-01
   1000 测试                2000-07-01  2000-10-01          3       4 2000-11-01
   1000 副经理             2001-01-01  2001-04-01          3       4 2001-05-01
   1000 DBA                2000-02-01  2000-03-01          1       4 2000-06-01
   1000 兼职经理          2000-03-01  2000-08-01          5       4 2000-07-01
   1000 经理                2001-05-01  2001-08-01          3       4 2001-09-01

30 rows selected

--
[/php]
剩下的工作就简单了，去除无效记录，再去重取数量即可：
[php]
JSSWEB> select count(unique am)
   2 from (select Months_between(end_date, start_date) mb,
   3                b.lv,
   4                add_months(a.start_date, lv) am
   5          from tmp3 a,
   6                (select level - 1 lv
   7                   from dual
   8                connect by rownum <=
   9                            (select max(Months_between(end_date, start_date)) mb
10                               from test t)) b) c
11 where c.mb > lv
12 order by am;

COUNT(UNIQUEAM)
---------------
         15
--
[/php]

junsansi · 发表于 2008-4-14 10:59

4、将列值为0的列替换为距离它最近列的非0值

记录集如下：
ADDDATE ADDVALUE
-------------- ---------------
2007-03-01 0
2007-03-02 0
2007-03-05 3.64
2007-03-06 3.82
2007-03-07 0
2007-03-08 3.47
2007-03-09 0
2007-03-12 0
2007-03-13 4.01
2007-03-14 4.21
2007-03-15 4.12
2007-03-16 0
2007-03-17 0

用SQL实现如下效果：
ADDDATE ADDVALUE
-------------- ---------------
2007-03-01 3.64
2007-03-02 3.64
2007-03-05 3.64
2007-03-06 3.82
2007-03-07 3.47
2007-03-08 3.47
2007-03-09 4.01
2007-03-12 4.01
2007-03-13 4.01
2007-03-14 4.21
2007-03-15 4.12
2007-03-16 0
2007-03-17 0

建表语句如下：
create table tmp4 (adddate varchar2(20),addvalue number);
insert into tmp4 values ('2007-03-01',0);
insert into tmp4 values ('2007-03-02',0);
insert into tmp4 values ('2007-03-05',3.64);
insert into tmp4 values ('2007-03-06',3.82);
insert into tmp4 values ('2007-03-07',0);
insert into tmp4 values ('2007-03-08',3.47);
insert into tmp4 values ('2007-03-09',0);
insert into tmp4 values ('2007-03-12',0);
insert into tmp4 values ('2007-03-13',4.01);
insert into tmp4 values ('2007-03-14',4.21);
insert into tmp4 values ('2007-03-15',4.12);
insert into tmp4 values ('2007-03-16',0);
insert into tmp4 values ('2007-03-17',0);
Commit;

解题思路：
别想歪了，这道题用lead,lag之类分析函数是不行地，费事又不讨好，最简单的方式，如果不考虑执行效率的话，可以这样：
[php]
JSSWEB> select a.adddate,
   2       decode(a.addvalue,0, nvl((select b.addvalue
   3          from tmp4 b
   4          where b.adddate > a.adddate
   5          and b.addvalue != 0
   6          and rownum = 1),0),a.addvalue) addvalue
   7 from tmp4 a
   8  ;

ADDDATE             ADDVALUE
-------------------- ----------
2007-03-01          3.64
2007-03-02          3.64
2007-03-05          3.64
2007-03-06          3.82
2007-03-07          3.47
2007-03-08          3.47
2007-03-09          4.01
2007-03-12          4.01
2007-03-13          4.01
2007-03-14          4.21
2007-03-15          4.12
2007-03-16          0
2007-03-17          0
--
[/php]

正如前文所说，这种方式效率实在堪忧，尤其是当tmp4记录量较大时，毕竟count(0)+1次tmp4表的扫描所花代价较大。
我们知道，上述形式的语句通常都是可以转换成连接查询的，因此，稍做转换：
[php]
JSSWEB> select ad1, decode(cw, 1, av1, 2, av2, 3, av1) adv
   2 from (select c.*, row_number() over(partition by ad1 order by ad2) rn
   3          from (select a.adddate ad1,
   4                      a.addvalue av1,
   5                      b.adddate ad2,
   6                      b.addvalue av2,
   7                      case
   8                         when a.addvalue != 0 then
   9                         1
10                         when b.adddate > a.adddate and a.addvalue = 0 then
11                         2
12                         when b.adddate is null and a.addvalue = 0 then
13                         3
14                         else
15                         0
16                      end as cw
17                   from tmp4 a, tmp4 b
18                where b.addvalue(+) != 0
19                and b.adddate(+)>a.adddate
20                order by a.adddate) c
21          where cw != 0)
22 where rn = 1
23  ;

AD1                      ADV
-------------------- ----------
2007-03-01                3.64
2007-03-02                3.64
2007-03-05                3.64
2007-03-06                3.82
2007-03-07                3.47
2007-03-08                3.47
2007-03-09                4.01
2007-03-12                4.01
2007-03-13                4.01
2007-03-14                4.21
2007-03-15                4.12
2007-03-16                   0
2007-03-17                   0

--
[/php]

稍加一点难度，如果希望的结果集是这样，又该怎么样实现呢：
ADDDATE ADDVALUE
-------------- ---------------
2007-03-01 3.64
2007-03-02 3.64
2007-03-05 3.64
2007-03-06 3.82
2007-03-07 3.47
2007-03-08 3.47
2007-03-09 4.01
2007-03-12 4.01
2007-03-13 4.01
2007-03-14 4.21
2007-03-15 4.12
2007-03-16 4.12
2007-03-17 4.12

并不困难，只要对我们的sql稍加改动即可
[php]
JSSWEB> select ad1,nav from(
   2  select c.*,row_number() over(partition by ad1 order by ad2) rn from (
   3  select a.adddate ad1,
   4       a.addvalue av1,
   5       b.adddate ad2,
   6       b.addvalue av2,
   7       case
   8          when a.addvalue != 0 then
   9          a.addvalue
10          when a.addvalue = 0 and b.adddate > a.adddate then
11          b.addvalue
12          when a.addvalue = 0 and b.adddate is null then
13          first_value(b.addvalue) over(order by b.adddate desc nulls last)
14          else
15          0
16       end nav
17 from tmp4 a, tmp4 b
18 where b.addvalue(+) != 0
19 and b.adddate(+)>a.adddate
20 order by 1
21  ) c where nav!=0)where rn=1
22  ;

AD1                      NAV
-------------------- ----------
2007-03-01                3.64
2007-03-02                3.64
2007-03-05                3.64
2007-03-06                3.82
2007-03-07                3.47
2007-03-08                3.47
2007-03-09                4.01
2007-03-12                4.01
2007-03-13                4.01
2007-03-14                4.21
2007-03-15                4.12
2007-03-16                4.12
2007-03-17                4.12
--
[/php]

mychary · 发表于 2008-4-14 11:00

支持lz

junsansi · 发表于 2008-4-14 11:01

5、行列转换，将列中字符串以'/'分隔，转换成行

记录集如下：
CODE
-------------------------------------
c001/c002/c007
c001/c003
c008/c0011/c029/c023
c004
c102/c111/c112/c144/c167
c008/c029/c023
c008
a/b/c/d/e/f/g/h/i

通过SQL实现如下结果集：
CODE
-------------------------------------
c002
e
h
i
c0011
d
c029
g
c102
a
b
c007
c
c144
c001
c111
c167
c004
c112
c008
c003
c023
f

建表语句如下：
create table tmp5 (code varchar2(50));
insert into tmp5 values ('c001/c002/c007');
insert into tmp5 values ('c001/c003');
insert into tmp5 values ('c008/c0011/c029/c023');
insert into tmp5 values ('c004');
insert into tmp5 values ('c102/c111/c112/c144/c167');
insert into tmp5 values ('c008/c029/c023');
insert into tmp5 values ('c008');
insert into tmp5 values ('a/b/c/d/e/f/g/h/i');
Commit;

解题思路：
行列转换不少朋友都比较熟悉了，虽然说应用的范围和机率非常低，但这确实是比较能够考查sql理解能力的方式，这道题与普通行转列的最大区别是转换后的行数不固定，看起来有点麻烦，但是如果你深入理解了第3个示例，再回过头来看这个，你一定会有种感觉：有点眉目了！

我们这里也借助第3例中所说的那种方式，先构造出一个足够行数的结果集出来：
[php]
JSSWEB> select code,rn
   2 from tmp5 a,
   3       (select rownum rn
   4          from dual
   5       connect by rownum <=
   6                   (select max(length(code) - length(replace(code, '/'))) + 1
   7                      from tmp5))
   8  ;

CODE                                                    RN
-------------------------------------------------- ----------
c001/c002/c007                                           1
c001/c003                                                 1
c008/c0011/c029/c023                                     1
c004                                                       1
c102/c111/c112/c144/c167                                  1
............
............
c008/c029/c023                                           9
c008                                                       9
a/b/c/d/e/f/g/h/i                                        9

72 rows selected
--
[/php]

然后就是根据rn+'/'的位置来判断每一行应截取的字符串，比如rn为1的时候，就截取从0到第一个'/'的字符，rn为2时，就截取从第rn-1到第rn个'/'字符间的位置，特别需要注意的是，一定要判断好起始位置和结束位置，这里呢，我们就分成两步：

第一步decode(rn, 1, 1, instr(code, '/', 1, rn - 1) + length('/'))计算出截取开始位置，instr(code, '/', 1, rn)结束位置
第二步执行字符串截取：substr(code,st,decode(en,0,length(code),en-st))

[php]
JSSWEB> select distinct substr(code,st,decode(en,0,length(code),en-st)) code from(
   2  select a.*,
   3       decode(rn, 1, 1, instr(code, '/', 1, rn - 1) + length('/')) st,
   4       instr(code, '/', 1, rn) en
   5 from (select code, rn
   6          from tmp5,
   7                (select rownum rn
   8                   from dual
   9                connect by rownum <= (select max(length(code) -
10                                                 length(replace(code, '/'))) + 1
11                                        from tmp5))) a)c
12  where instr(substr(code,st,decode(en,0,length(code),en-st)),'/')=0
13  ;

CODE
--------------------------------------------------------------------------------
c002
e
h
i
c0011
d
c029
g
c102
a
b
c007
c
c144
c001
c111
c167
c004
c112
c008
c003
c023
f

23 rows selected
--
[/php]

默认没有排序，当然，想要实现排序也非常简单，只要在适当位置引入rn即可轻松实现，怎么样，亲自动手去试试吧