szhswl
          宋針還的個(gè)人空間
          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說(shuō)明:將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說(shuō)明:有三個(gè)表sys_user_role_tb用戶信息表,sys_role_info_tb角色信息表,sys_user_role_tb用戶角色關(guān)聯(lián)表,
          此SQL實(shí)現(xiàn)了role_name在同一單元中以“,”相連顯示。


          ---------------------------------------------------------------------------------------------------------------------------------
          說(shuō)人之短,乃護(hù)己之短??浼褐L(zhǎng),乃忌人之長(zhǎng)。皆由存心不厚,識(shí)量太狹耳。能去此弊,可以進(jìn)德,可以遠(yuǎn)怨。
          http://www.aygfsteel.com/szhswl
          ------------------------------------------------------------------------------------------------------ ----------------- ---------
          posted on 2007-12-03 15:19 宋針還 閱讀(594) 評(píng)論(0)  編輯  收藏 所屬分類: SQL
          主站蜘蛛池模板: 昆山市| 文水县| 工布江达县| 福州市| 靖边县| 延边| 公主岭市| 玉林市| 拉萨市| 禄劝| 都昌县| 苍山县| 确山县| 米易县| 高唐县| 云梦县| 庄河市| 连江县| 康保县| 丰台区| 海原县| 阿克苏市| 平利县| 安化县| 沭阳县| 澜沧| 望江县| 社会| 威海市| 格尔木市| 罗山县| 禄丰县| 贺兰县| 灌南县| 武汉市| 陆川县| 增城市| 凤山县| 榆中县| 黄平县| 中方县|