<!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} @font-face {font-family:"/@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:宋体; mso-font-kerning:1.0pt;} /* Page Definitions */ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page Section1 {size:21.0cm 842.0pt; margin:72.0pt 89.85pt 72.0pt 89.85pt; mso-header-margin:42.55pt; mso-footer-margin:49.6pt; mso-paper-source:0; layout-grid:15.6pt;} div.Section1 {page:Section1;} -->
最近遇到了一个问题,需要将查询出来的结果中某一字段的值合并起来显示,通过自己的努力有了个Workaround ,记下来以备后忘。
下面是数据库中的数据存在形式,用普通的语句查询如下
Select Iq_No,Report_No from Iq_Body
查询出的结果:
Iq_No Report_no
--------------------------------------------------
N3204101090421000002 320410109081597
N3204101090421000002 320410109081598
N3204101090421000002 320410109081599
N3204101090421000003 320410109081600
N3204101090421000003 320410109081124
实际想要的结果:
Iq_No Report_No
--------------------------------------------
N3204101090421000002 320410109081597 , 320410109081598 , 320410109081599
N3204101090421000003 320410109081600 , 320410109081124
用下面的SQL 语句可以得到想要的结果:
SQL 语句
1 SELECT IQ_NO,
2 REPLACE (SUBSTR( MAX ( CASE
3 WHEN REPORT_NO IS NOT NULL OR LENGTH(REPORT_NO) = 0 THEN
4 SYS_CONNECT_BY_PATH(REPORT_NO, ';' )
5 END ),
6 2 ),
7 ';' ,
8 ',' ) REPORT_NO
9 FROM ( SELECT IQ_NO,
10 REPORT_NO,
11 rn,
12 LEAD(rn) OVER (PARTITION BY IQ_NO ORDER BY rn) rn1
13 FROM ( SELECT IQ_NO,
14 REPORT_NO,
15 ROW_NUMBER() OVER ( ORDER BY REPORT_NO DESC ) rn
16 FROM IQ_BODY))
17 START WITH rn1 IS NULL
18 CONNECT BY rn1 = PRIOR rn
19 GROUP BY IQ_NO