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字段排序后显示在同一单元中,显示结果:
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用户角色关联表,
此SQL实现了role_name在同一单元中以“,”相连显示。


---------------------------------------------------------------------------------------------------------------------------------
说人之短,乃护己之短。夸己之长,乃忌人之长。皆由存心不厚,识量太狭耳。能去此弊,可以进德,可以远怨。
http://www.blogjava.net/szhswl
------------------------------------------------------------------------------------------------------ ----------------- ---------
posted on 2007-12-03 15:19 宋针还 阅读(546) 评论(0)  编辑  收藏 所属分类: SQL

只有注册用户登录后才能发表评论。


网站导航: