szhswl
          宋針還的個人空間
          select substr(max(sys_connect_by_path(upload_num_count, ',')), 2) result
            from (select id, upload_num_count, rn, lead(rn) over(partition by id order by rn) rn1
                    from (select 1 id, upload_num_count, row_number() over(order by upload_num_count desc) rn
                            from adp_yellowpage_sr))
           start with rn1 is null
          connect by rn1 = prior rn
          SQL說明:將adp_yellowpage_sr表中的upload_num_count字段排序后顯示在同一單元中,顯示結(jié)果:
          0,0,0,0,1,11,11,11,11,11,11,11,11,11,11,11,11,11,85,254,254,254,352,,,,,

          select id,
                 login_id,
                 job_number,
                 user_name,
                 user_mobile,
                 user_duty,
                 user_status,
                 max(ltrim(sys_connect_by_path(role_name, ','), ',')) role_name
            from (select usertb.id,
                         usertb.login_id,
                         usertb.job_number,
                         usertb.user_name,
                         usertb.user_mobile,
                         usertb.user_duty,
                         usertb.user_status,
                         roletb.role_name,
                         row_number() over(partition by usertb.id order by roletb.role_name) rn
                    from sys_user_info_tb usertb
                    left outer join sys_user_role_tb userrole on userrole.user_id =
                                                                 usertb.id
                    left outer join sys_role_info_tb roletb on roletb.id =
                                                               userrole.role_id)
           start with rn = 1
          connect by prior rn + 1 = rn
                 and prior id = id
           group by id,
                    login_id,
                    job_number,
                    user_name,
                    user_mobile,
                    user_duty,
                    user_status
          SQL說明:有三個表sys_user_role_tb用戶信息表,sys_role_info_tb角色信息表,sys_user_role_tb用戶角色關(guān)聯(lián)表,
          此SQL實現(xiàn)了role_name在同一單元中以“,”相連顯示。


          ---------------------------------------------------------------------------------------------------------------------------------
          說人之短,乃護(hù)己之短。夸己之長,乃忌人之長。皆由存心不厚,識量太狹耳。能去此弊,可以進(jìn)德,可以遠(yuǎn)怨。
          http://www.aygfsteel.com/szhswl
          ------------------------------------------------------------------------------------------------------ ----------------- ---------
          posted on 2007-12-03 15:19 宋針還 閱讀(595) 評論(0)  編輯  收藏 所屬分類: SQL
          主站蜘蛛池模板: 威宁| 淮滨县| 蒲江县| 郑州市| 湾仔区| 土默特右旗| 郧西县| 建德市| 蒙阴县| 赤城县| 焉耆| 钟祥市| 密山市| 花莲市| 革吉县| 河南省| 绥德县| 长葛市| 麦盖提县| 皮山县| 通辽市| 清远市| 平乡县| 南阳市| 建水县| 黎城县| 徐州市| 襄樊市| 泸州市| 榆林市| 门头沟区| 东辽县| 长海县| 阳原县| 深水埗区| 旅游| 长寿区| 兴国县| 唐河县| 南开区| 久治县|