单条SQL语句实现复杂逻辑几例~~ - Oracle开发 - ITPUB论坛

ITPUB论坛 » Oracle开发 » 单条SQL语句实现复杂逻辑几例~~

评价 \| 打印 \| 推荐 \| 订阅 \| 收藏标题: [精华] 单条SQL语句实现复杂逻辑几例~~
本主题由 zhangfengh 于 2008-4-14 19:18 加入精华

junsansi
无名扫把

来自 bj
精华贴数 7
个人空间 9096
技术积分 8237 (153)
社区积分 687 (1173)
注册日期 2006-7-17
论坛徽章:66

使用道具

发表于 2008-4-14 10:55

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

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

__________________

沉淀是一件很枯燥的事情，我要从沉淀中汲取营养，于枯燥中寻找乐趣，我没疯，你才是疯子，哇哈哈，哇哈哈哈哈~~
============================================
http://www.5ienet.com我爱我佳
扫地不过是我表面上的工作，我真正的身份其实是无名老僧------------------手里那根扫把~~~~

只看该作者

junsansi
无名扫把

来自 bj
精华贴数 7
个人空间 9096
技术积分 8237 (153)
社区积分 687 (1173)
注册日期 2006-7-17
论坛徽章:66

使用道具

发表于 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 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


--

__________________

只看该作者

junsansi
无名扫把

来自 bj
精华贴数 7
个人空间 9096
技术积分 8237 (153)
社区积分 687 (1173)
注册日期 2006-7-17
论坛徽章:66

使用道具

发表于 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 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


--

__________________

只看该作者

junsansi
无名扫把

来自 bj
精华贴数 7
个人空间 9096
技术积分 8237 (153)
社区积分 687 (1173)
注册日期 2006-7-17
论坛徽章:66

使用道具

发表于 2008-4-14 10:59

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

记录集如下：
ADDDATE