ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一例“诡异”报表SQL需求分析

一例“诡异”报表SQL需求分析

原创 Linux操作系统 作者:realkid4 时间:2013-07-07 17:10:17 0 删除 编辑

我们在实际的工作中,经常会遇到各式各样的报表结构和需求。让报表能够生成、高效的生成取决于多种因素,包括业务需求的平衡折中、支持架构设计和详细数据库设计权衡等。最直接我们遇到的问题,就是SQL语句的实现。本篇介绍一个实际的案例(经过修改),希望能够为将来有需要的朋友提供绵薄之力。

 

1、问题概述和需求

 

我们依然选在Oracle11gR2进行试验。

 

 

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE        11.2.0.1.0         Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

 

 

数据表结构涉及到三个表内容:t_mastert_child1t_child2。两个child表是t_master表的子表结构,通过外键链接。T_child1t_child2之间没有数量关系,m*n

 

 

SQL> create table t_master(id number, mas_name varchar2(10));

Table created

 

SQL> alter table t_master add constraint pk_t_master primary key (id);

Table altered

 

SQL> create table t_child1 (id number, mid number, child1_name  varchar2(10));

Table created

 

SQL> alter table t_child1 add constraint pk_t_child1 primary key (id);

Table altered

 

SQL> alter table t_child1 add constraint fk_child1_master foreign key (mid) references  t_master(id);

Table altered

 

 

SQL> create table t_child2 (id number, mid number, child2_name varchar2(10));

Table created

 

SQL> alter table t_child2 add constraint pk_child2 primary key (id);

Table altered

 

SQL> alter table t_child2 add constraint fk_child2_master foreign key (mid) references t_master(id);

Table altered

 

 

数据表内容描述。

 

 

SQL> select * from t_master;

        ID MAS_NAME

---------- ----------

         1 1_name

         2 2_name

         3 3_name

         4 4_name

         5 5_name

         6 6_name

 

6 rows selected

 

SQL> select * from t_child2;

        ID        MID CHILD2_NAME

---------- ---------- -----------

         1          1 1

         2          1 123

         3          1 123

         4          2 df

         5          2 dff

         6          3 fse

 

6 rows selected

 

 

SQL> select * from t_child1 order by mid;

        ID        MID CHILD1_NAME

---------- ---------- -----------

         1          1 kll

         3          1 dfell

         2          2 dfkll

         4          3 fwe

         5          4 fwe

         6          5 few

         7          5 fewd

 

7 rows selected

 

两个子表通过id列进行连接,同一个t_mater对一个的子表取值数量是不一定的。那么,报表需求是什么呢?

 

 

        ID MAS_NAME   CHILD1_NAME CHILD2_NAME

---------- ---------- ----------- -----------

         1 1_name     kll         1

         1 1_name     dfell       123

         1 1_name                 123

         2 2_name     dfkll       df

         2 2_name                 dff

         3 3_name     fwe         fse

         4 4_name     fwe        

         5 5_name     few        

         5 5_name     fewd       

         6 6_name                

 

 

两个子表的列child1child2以列的方式作为结果集合。但是最大难点在于列的数量,同一个t_master的取值主键对应的数据行数,是通过子表行数最大的一个进行确认。例如:t_masterid=1对应child1child2的个数分别为2条和3条。结果要显示成3行,并且child1对应的位置要显示为空。

 

2、抽丝剥茧、层层递进

 

我们最常见处理复杂SQL的方法就是抽丝剥茧、层层递进,不断靠近我们的目标集合。

 

首先,我们一般意义上将三个表连接起来的方法,不是很好用。

 

SQL> select m.id, m.mas_name, c1.child1_name, c2.child2_name

  2  from t_master m,

  3       t_child1 c1,

  4       t_child2 c2

  5  where m.id=c1.mid

  6    and m.id=c2.mid;

 

        ID MAS_NAME   CHILD1_NAME CHILD2_NAME

---------- ---------- ----------- -----------

         1 1_name     kll         123

         1 1_name     kll         123

         1 1_name     kll         1

         2 2_name     dfkll       dff

         2 2_name     dfkll       df

         1 1_name     dfell       123

         1 1_name     dfell       123

         1 1_name     dfell       1

         3 3_name     fwe         fse

 

9 rows selected

 

 

 

SQL> select m.id, m.mas_name, c1.child1_name, c2.child2_name

  2  from t_master m

  3  left join t_child1 c1

  4  on c1.mid=m.id

  5  left join t_child2 c2

  6  on c2.mid=m.id;

 

        ID MAS_NAME   CHILD1_NAME CHILD2_NAME

---------- ---------- ----------- -----------

         1 1_name     kll         123

         1 1_name     kll         123

         1 1_name     kll         1

         2 2_name     dfkll       dff

         2 2_name     dfkll       df

         1 1_name     dfell       123

         1 1_name     dfell       123

         1 1_name     dfell       1

         3 3_name     fwe         fse

         4 4_name     fwe        

         5 5_name     few        

         5 5_name     fewd       

         6 6_name                

 

13 rows selected

 

 

两种结构都有问题。第一种结构在于没有将不包括子记录的t_master记录显示出来。而第二种结构的问题在于一个对应id值,只能有一个t_child1t_child2记录,不能重合。

 

那么,怎么办呢?

 

从结果集合的情况看,单次t_mastert_child1/t_child2进行关联,可以构成一半近似的结果集合。

 

 

SQL> select m.id, m.mas_name, c1.child1_name

  2  from t_master m

  3  left join t_child1 c1

  4  on m.id=c1.mid;

 

        ID MAS_NAME   CHILD1_NAME

---------- ---------- -----------

         1 1_name     kll

         1 1_name     dfell

         2 2_name     dfkll

         3 3_name     fwe

         4 4_name     fwe

         5 5_name     few

         5 5_name     fewd

         6 6_name    

 

8 rows selected

 

 

如果单独构建结果集合,并且合并起来,看来可以得到类似的结果。

 

 

SQL> select *

  2  from

  3  (select m.id, m.mas_name, c1.child1_name, rownum nm

  4   from t_master m

  5   left join t_child1 c1

  6   on m.id=c1.mid

  7   where m.id=1) part1,

  8  (select m.id, m.mas_name, c2.child2_name, rownum nm

  9   from t_master m

 10   left join t_child2 c2

 11   on m.id=c2.mid

 12   where m.id=1) part2

 13  where part1.id=part2.id

 14    and part1.nm=part2.nm;

 

        ID MAS_NAME   CHILD1_NAME         NM         ID MAS_NAME   CHILD2_NAME         NM

---------- ---------- ----------- ---------- ---------- ---------- ----------- ----------

         1 1_name     kll                1          1 1_name     1            1

         1 1_name     dfell       2          1 1_name     123                  2

 

 

注意,为了进行试验,我们选择了一个t_masterid进行试验。我们在两个部分结果集合中使用rownum进行排序,合并条件设置为t_masterrm相同。但是这个结果集合也有一些问题,就是id=1对应的子表是23条记录,结果集不正确。解决的方法是使用full outer join方法。

 

 

 

SQL> select part1.id, part1.mas_name, part1.child1_name, part2.child2_name

  2  from

  3  (select m.id, m.mas_name, c1.child1_name, rownum nm

  4   from t_master m

  5   left join t_child1 c1

  6   on m.id=c1.mid

  7   where m.id=1) part1

  8  full outer join

  9  (select m.id, m.mas_name, c2.child2_name, rownum nm

 10   from t_master m

 11   left join t_child2 c2

 12   on m.id=c2.mid

 13   where m.id=1) part2

 14  on part1.id=part2.id and part1.nm=part2.nm;

 

        ID MAS_NAME   CHILD1_NAME CHILD2_NAME

---------- ---------- ----------- -----------

         1 1_name     kll         1

         1 1_name     dfell       123

                                  123

 

 

离正确结果接近了一步。问题有两个目前,一个是IDMAS_NAME还没有正确配合结果,有的数据行还显示为空。另一个提出id=1的条件之后,rownum就不能使用了。

 

下一步,使用nvl能解决第一个问题。

 

 

SQL> select nvl(part1.id,part2.id) ID, nvl(part1.mas_name,part2.mas_name) MAS_NAME,

  2         part1.child1_name, part2.child2_name

  3  from

  4  (select m.id, m.mas_name, c1.child1_name, rownum nm

  5   from t_master m

  6   left join t_child1 c1

  7   on m.id=c1.mid

  8   where m.id=1) part1

  9  full outer join

 10  (select m.id, m.mas_name, c2.child2_name, rownum nm

 11   from t_master m

 12   left join t_child2 c2

 13   on m.id=c2.mid

 14   where m.id=1) part2

 15  on part1.id=part2.id and part1.nm=part2.nm;

 

        ID MAS_NAME   CHILD1_NAME CHILD2_NAME

---------- ---------- ----------- -----------

         1 1_name     kll         1

         1 1_name     dfell       123

         1 1_name                 123

 

 

3、最后结果

 

替换掉rownum的确比较费时间。和rownum类似的一种高级功能就是row_number分析函数。分析函数可以实现分区内详细的行计数功能。

 

下面可以展示下row_number的功能。

 

 

SQL> select m.id, m.mas_name, c1.child1_name, row_number()over(partition by m.id order by c1.id) nm

  2   from t_master m

  3   left join t_child1 c1

  4   on m.id=c1.mid

  5  ;

 

        ID MAS_NAME   CHILD1_NAME         NM

---------- ---------- ----------- ----------

         1 1_name     kll                  1

         1 1_name     dfell                2

         2 2_name     dfkll                1

         3 3_name     fwe                  1

         4 4_name     fwe                  1

         5 5_name     few                  1

         5 5_name     fewd                 2

         6 6_name                          1

 

8 rows selected

 

 

Row_number函数可以实现在一个数据范围(partition)内的计数行功能。加入到我们的SQL中,如下。

 

 

SQL> select nvl(part1.id,part2.id) ID, nvl(part1.mas_name,part2.mas_name) MAS_NAME,

  2         part1.child1_name, part2.child2_name

  3  from

  4  (select m.id, m.mas_name, c1.child1_name, row_number()over(partition by m.id order by c1.id) nm

  5   from t_master m

  6   left join t_child1 c1

  7   on m.id=c1.mid

  8   ) part1

  9  full outer join

 10  (select m.id, m.mas_name, c2.child2_name, row_number()over(partition by m.id order by c2.id) nm

 11   from t_master m

 12   left join t_child2 c2

 13   on m.id=c2.mid

 14   ) part2

 15  on part1.id=part2.id and part1.nm=part2.nm

 16  order by ID;

 

        ID MAS_NAME   CHILD1_NAME CHILD2_NAME

---------- ---------- ----------- -----------

         1 1_name     kll         1

         1 1_name     dfell       123

         1 1_name                 123

         2 2_name     dfkll       df

         2 2_name                 dff

         3 3_name     fwe         fse

         4 4_name     fwe        

         5 5_name     few        

         5 5_name     fewd       

         6 6_name                 

 

10 rows selected

 

 

需求实现。

 

4、结论

 

在应用系统中,报表往往是重要的功能模块。如何实现报表需求,要把握一个尺度,就是“尽量不要将一句SQL的功能实现为多条”。这就考验我们的SQL功底和技术。使用抽丝剥茧、层层递进的方法,可以方便的将我们的应用需求加以实现。

 

 

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

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7630266