表A 用户表 主键 user_id
user_id, user_name ...
001 , 张三
002 , 李四
表B 角色表 通过user_id与用户表关联
id , user_id , role_code
1 , 001 , r001
2 , 001 , r002
3 , 002 , r001
表C 角色字典表
id , role_code , role_name
1 , r001 , 普通用户
2 , r002 , 系统管理员我现在需要查询每个用户的详细信息,比如
user_id , user_name , role_name(这个如果有多项可以使用 , 隔开)
user_id, user_name ...
001 , 张三
002 , 李四
表B 角色表 通过user_id与用户表关联
id , user_id , role_code
1 , 001 , r001
2 , 001 , r002
3 , 002 , r001
表C 角色字典表
id , role_code , role_name
1 , r001 , 普通用户
2 , r002 , 系统管理员我现在需要查询每个用户的详细信息,比如
user_id , user_name , role_name(这个如果有多项可以使用 , 隔开)
SELECT a.userid,a.user_name,c.role_name
FROM 表A a ,表B b,表C c
WHERE a.user_id=b.user_id
and b.role_code=c.role_code
SELECT a.[user_id],a.[user_name],c.role_name
FROM 表A a ,表B b,表C c
WHERE a.[user_id]=[b.user_id]
and b.role_code=c.role_code
SELECT a.user_id,a.user_name,c.role_name
FROM 表A a ,表B b,表C c
WHERE a.user_id=b.user_id
and b.role_code=c.role_code
亦可使用left join
select 'A' grade,'XX'name from dual union all
select 'A','XY' from dual union all
select 'A','YY' from dual union all
select 'B','aa' from dual union all
select 'B','bb' from dual)
select grade,substr(max(sys_connect_by_path(name,';')),2) name
from (select grade,name,row_number() over(partition by grade order by name) rn from tb)
start with rn=1
connect by rn= prior rn+1 and
connect_by_root(grade)=grade
group by grade;
--
GRADE NAME
----- --------------------------------------------------------------------------------
A XX;XY;YY
B aa;bb
--
10g的实现:
with t as(
select 'A' grade,'XX'name from dual union all
select 'A','XY' from dual union all
select 'A','YY' from dual union all
select 'B','aa' from dual union all
select 'B','bb' from dual)
select grade,wmsys.wm_concat(name) name
from t
group by grade;
--
GRADE NAME
----- --------------------------------------------------------------------------------
A XX,XY,YY
B aa,bb
SELECT A.USER_ID,A.USER_NAME,WM_CONCAT(C.ROLE_NAME) FROM A,B,C WHERE A.USER_ID = B.USER_ID AND B.ROLE_CODE = C.ROLE_CODE GROUP BY A.USER_ID,A.USER_NAME;
create or replace type string_sum_obj as object (
--聚合函数的实质就是一个对象
sum_string varchar2(4000),
static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number,
--对象初始化
member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number,
--聚合函数的迭代方法(这是最重要的方法)
member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number,
--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number
--终止聚集函数的处理,返回聚集函数处理的结果.
)CREATE OR REPLACE FUNCTION FN_CONCAT(VALUE VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING STRING_SUM_OBJ;
with tmp as
(select 1 order_id, 'aa' cc_content
from dual
union all
select 1 order_id, 'bb' cc_content
from dual
union all
select 1 order_id, 'cc' cc_content
from dual
union all
select 2 order_id, 'dd' cc_content
from dual
union all
select 2 order_id, 'ee' cc_content
from dual
union all
select 4 order_id, 'ff' cc_content
from dual
union all
select 4 order_id, 'gg' cc_content from dual)select a.order_id, cc_content
from (select order_id,
substr(sys_connect_by_path(cc_content, ','), 2) cc_content,
level le
from (select row_number() over(order by order_id) rn, t.*
from tmp t)
connect by prior rn + 1 = rn
and prior order_id = order_id) a,
(select order_id, count(cc_content) co from tmp group by order_id) b
where a.le = b.co
and a.order_id = b.order_id;