ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 数据库开发技术 > 单条SQL语句实现复杂逻辑几例~~

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

原创 数据库开发技术 作者：asword 时间：2008-08-29 15:04:18 0 删除 编辑

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

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

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

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

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

[@more@]

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

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

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

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

--
记录集如下：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

--

                                                    

                                        

                    
                    

                                                    来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/82387/viewspace-1009790/
                            ，如需转载，请注明出处，否则将追究法律责任。
                                            


                

            

                            

                    
                    
0

                    
0

                    

                


                

                    

                        上一篇：
                                                    一个树形聚集SQL问题（二） (物料BOM消耗量计算) zt
                        
                    

                    

                        下一篇：
                                                    使用Oracle Hint提示来更改执行计划 ZT
                                            

                


                


                
                

                    

                        
                        
                        
                            
                        

                        

                            请登录后发表评论
                            
                                登录
                            
                        


                        
                        


                    

                    

                        
全部评论 
                        

                        

                            
                        


                    


                

                
                    

        
        

            
            


        

        
    

    
asword

            

        
    

            
注册时间：2009-03-17

            
    
        
  • 
            
    博文量
    
            137
        
    • 
        
  • 
            
    访问量
    
            
    399355
    
        
    • 
    






            

                            
                

                    
最新文章

                    

                
                    

    

    

    

    
            
    



    

        

            
                

                    
                

            
            
        

        

            支持我们
            作者招募
            用户协议
            FAQ
            Contact Us
            
        

        
北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8  北京市公安局海淀分局网监中心备案编号：11010802021510  niuxiaotong@pcpop.com  17352615567
             未成年人举报专区

        
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

        
违法和不良信息举报电话:18101376593