我们在实际的工作中，经常会遇到各式各样的报表结构和需求。让报表能够生成、高效的生成取决于多种因素，包括业务需求的平衡折中、支持架构设计和详细数据库设计权衡等。最直接我们遇到的问题，就是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_master，t_child1和t_child2。两个child表是t_master表的子表结构，通过外键链接。T_child1和t_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

两个子表的列child1和child2以列的方式作为结果集合。但是最大难点在于列的数量，同一个t_master的取值主键对应的数据行数，是通过子表行数最大的一个进行确认。例如：t_master中id=1对应child1和child2的个数分别为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 1

2 2_name dfkll dff

2 2_name dfkll df

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 1

2 2_name dfkll dff

2 2_name dfkll df

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_child1和t_child2记录，不能重合。

那么，怎么办呢？

从结果集合的情况看，单次t_master和t_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_master的id进行试验。我们在两个部分结果集合中使用rownum进行排序，合并条件设置为t_master和rm相同。但是这个结果集合也有一些问题，就是id=1对应的子表是2和3条记录，结果集不正确。解决的方法是使用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

离正确结果接近了一步。问题有两个目前，一个是ID和MAS_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功底和技术。使用抽丝剥茧、层层递进的方法，可以方便的将我们的应用需求加以实现。

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