一例“詭異”報表SQL需求分析
我們在實際的工作中，經常會遇到各式各樣的報表結構和需求。讓報表能夠生成、高效的生成取決於多種因素，包括業務需求的平衡折中、支援架構設計和詳細資料庫設計權衡等。最直接我們遇到的問題，就是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 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_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功底和技術。使用抽絲剝繭、層層遞進的方法，可以方便的將我們的應用需求加以實現。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-765688/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【PL/SQL開發】-----詭異啊SQL
- 很詭異的博弈問題分析方法
- MySQL update一個詭異現象的分析--個人未分析出MySql
- 詭異的”慢查詢“
- C語言之詭異字串C語言字串
- JavaScript 詭異的0.01JavaScript
- 一個詭異的 Pulsar InterruptedException 異常Exception
- 記錄一次詭異的拼接sql不生效問題SQL
- 詭異！React stopPropagation失靈React
- GP詭異的查詢轉換
- 詭異的無線網路卡Down
- 詭異的磁碟空間100%報警分析得出df -h與du -sh *的根本性差別
- 如何實現報表直接列印需求
- 跑批SQL效能異常分析SQL
- python 詭異問題求助各位大哥Python
- API 路由中介軟體的詭異API路由
- 介面詭異的404問題記錄
- 詭異的HP-UX Load averagesUX
- 如何實現報表的批次列印需求
- 報表合計需求的實現方法
- 動態sql 報表SQL
- 如何實現報表的點選表頭排序需求排序
- 專案需求分析報告怎麼做
- 攔截線上 sql 異常報錯SQL
- PL/SQL Profiler 和SQL Developer 報表SQLDeveloper
- 一個執行緒罷工的詭異事件執行緒事件
- 詭異！std::bind in std::bind 編譯失敗編譯
- 執行緒同步的詭異：求指點執行緒
- 詭異的事情，RAC，public ip通，vip不通
- oracle 異構平臺遷移之傳輸表空間一例Oracle
- 財務報表分析
- 庫存分析報表
- 需求分析
- Linux終端的8個詭異傢伙Linux
- 記一次詭異的故障排查經歷
- 由optimizer_switch所引起的詭異問題
- 一個看似詭異的Oracle連線問題Oracle
- 財務報表分析是在分析什麼？如何選擇財務報表分析工具