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 code:



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


--

这跟预想的结果有不小差距。

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

PHP code:



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


--

然后在做sum的时候，我们只需要判断一下，比如产品为彩电或微波炉的话，sum(ns)，否则sum(sales)，最后按照城市产品和年份排下序即可：

PHP code:



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


--

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

下面再介绍一种更简单也更高效的实现方式，借助model和partition，可以直接指定计算规则：

PHP code:



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 ro

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

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 code:



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


--

*rownum列是为了排序用

然后生成用于partition的列

PHP code:



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





--

这下就清晰多了，剩下的就没难度了，count() over()生成数量，取数量大于2的记录即可：

PHP code:



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


--

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 code:



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


--

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

然后与tmp3表做Cartesian，即能够生成比我们希望数量还要多的结果集(多不怕，别少就行啊)：

PHP code:



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 code:



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


--

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 code:



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


--

正如前文所说，这种方式效率实在堪忧，尤其是当tmp4记录量较大时，毕竟count(0)+1次tmp4表的扫描所花代价较大。
我们知道，上述形式的语句通常都是可以转换成连接查询的，因此，稍做转换：

PHP code:



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





--

稍加一点难度，如果希望的结果集是这样，又该怎么样实现呢：
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 code:



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


--

支持lz

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 code:



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


--

然后就是根据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 code:



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


--

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

打包成pdf鸟

QUOTE:

---

原帖由 junsansi 于 2008-4-14 11:25 发表
打包成pdf鸟

---

谢谢分享, 看看先

有赚回复分的嫌疑

Adobe Reader 7.0打不开, 文件损坏

不错,MODEL没用过,学习了