查看: 34998|回复: 79

[精华] 单条SQL语句实现复杂逻辑几例~~

[复制链接]
论坛徽章:
281
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-01-04 11:51:22蛋疼蛋
日期:2011-12-29 07:37:22迷宫蛋
日期:2011-12-26 14:19:41茶鸡蛋
日期:2011-11-17 09:20:52茶鸡蛋
日期:2011-11-10 22:42:38ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15茶鸡蛋
日期:2011-10-24 09:48:48ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47
发表于 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
论坛徽章:
281
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-01-04 11:51:22蛋疼蛋
日期:2011-12-29 07:37:22迷宫蛋
日期:2011-12-26 14:19:41茶鸡蛋
日期:2011-11-17 09:20:52茶鸡蛋
日期:2011-11-10 22:42:38ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15茶鸡蛋
日期:2011-10-24 09:48:48ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47
 楼主| 发表于 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]

使用道具 举报

回复
论坛徽章:
281
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-01-04 11:51:22蛋疼蛋
日期:2011-12-29 07:37:22迷宫蛋
日期:2011-12-26 14:19:41茶鸡蛋
日期:2011-11-17 09:20:52茶鸡蛋
日期:2011-11-10 22:42:38ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15茶鸡蛋
日期:2011-10-24 09:48:48ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47
 楼主| 发表于 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]

使用道具 举报

回复
论坛徽章:
281
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-01-04 11:51:22蛋疼蛋
日期:2011-12-29 07:37:22迷宫蛋
日期:2011-12-26 14:19:41茶鸡蛋
日期:2011-11-17 09:20:52茶鸡蛋
日期:2011-11-10 22:42:38ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15茶鸡蛋
日期:2011-10-24 09:48:48ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47
 楼主| 发表于 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]

使用道具 举报

回复
论坛徽章:
44
双鱼座
日期:2016-01-07 20:57:31奔驰
日期:2013-08-02 22:22:552013年新春福章
日期:2013-02-25 14:51:24迷宫蛋
日期:2013-01-29 22:12:11蛋疼蛋
日期:2013-01-07 15:50:53ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28紫蛋头
日期:2011-07-31 11:27:01蜘蛛蛋
日期:2011-06-14 14:20:33蛋疼蛋
日期:2011-06-03 19:39:27SQL大赛参与纪念
日期:2011-04-13 12:08:17
发表于 2008-4-14 11:00 | 显示全部楼层
支持lz

使用道具 举报

回复
论坛徽章:
281
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-01-04 11:51:22蛋疼蛋
日期:2011-12-29 07:37:22迷宫蛋
日期:2011-12-26 14:19:41茶鸡蛋
日期:2011-11-17 09:20:52茶鸡蛋
日期:2011-11-10 22:42:38ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15茶鸡蛋
日期:2011-10-24 09:48:48ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47
 楼主| 发表于 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即可轻松实现,怎么样,亲自动手去试试吧

使用道具 举报

回复
论坛徽章:
281
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-01-04 11:51:22蛋疼蛋
日期:2011-12-29 07:37:22迷宫蛋
日期:2011-12-26 14:19:41茶鸡蛋
日期:2011-11-17 09:20:52茶鸡蛋
日期:2011-11-10 22:42:38ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15茶鸡蛋
日期:2011-10-24 09:48:48ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47
 楼主| 发表于 2008-4-14 11:25 | 显示全部楼层
打包成pdf鸟

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

197.01 KB, 下载次数: 1039

使用道具 举报

回复
论坛徽章:
18
生肖徽章2007版:虎
日期:2008-04-11 18:37:24奥运会纪念徽章:击剑
日期:2008-07-03 11:38:17迷宫蛋
日期:2011-05-10 13:03:40茶鸡蛋
日期:2011-05-10 13:05:16蜘蛛蛋
日期:2011-05-10 13:07:01灰彻蛋
日期:2012-12-10 11:47:16鲜花蛋
日期:2013-07-07 10:07:20
发表于 2008-4-14 11:33 | 显示全部楼层
原帖由 junsansi 于 2008-4-14 11:25 发表
打包成pdf鸟


谢谢分享, 看看先

有赚回复分的嫌疑

使用道具 举报

回复
论坛徽章:
18
生肖徽章2007版:虎
日期:2008-04-11 18:37:24奥运会纪念徽章:击剑
日期:2008-07-03 11:38:17迷宫蛋
日期:2011-05-10 13:03:40茶鸡蛋
日期:2011-05-10 13:05:16蜘蛛蛋
日期:2011-05-10 13:07:01灰彻蛋
日期:2012-12-10 11:47:16鲜花蛋
日期:2013-07-07 10:07:20
发表于 2008-4-14 11:34 | 显示全部楼层
Adobe Reader 7.0打不开, 文件损坏

使用道具 举报

回复
论坛徽章:
34
劳斯莱斯
日期:2013-08-08 14:01:23三菱
日期:2013-09-28 10:16:06一汽
日期:2013-11-19 17:01:11凯迪拉克
日期:2013-12-07 17:11:282014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02itpub13周年纪念徽章
日期:2014-09-27 14:20:21itpub13周年纪念徽章
日期:2014-10-08 15:13:38懒羊羊
日期:2015-03-04 14:52:112015年新春福章
日期:2015-03-06 11:58:18
发表于 2008-4-14 11:44 | 显示全部楼层
不错,MODEL没用过,学习了

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表