table1:
uID uName
1 小李
2 小张 table2:
pID uID type
1 1 H1
2 2 H2
table3:
uID teamID
1 T1
1 T2
1 T4
2 T1
2 T3
2 T4
2 T5table4:
teamID teamName
T1 医疗
T2 救护
T3 急救
T4 医药
T5 其他现在想要一条SQL查询出如下的结果uID uName team type
1 小李 医疗,救护,医药 H1
1 小涨 医疗,急救,医药,其他 H2请高手们不吝赐教~~感激不尽
uID uName
1 小李
2 小张 table2:
pID uID type
1 1 H1
2 2 H2
table3:
uID teamID
1 T1
1 T2
1 T4
2 T1
2 T3
2 T4
2 T5table4:
teamID teamName
T1 医疗
T2 救护
T3 急救
T4 医药
T5 其他现在想要一条SQL查询出如下的结果uID uName team type
1 小李 医疗,救护,医药 H1
1 小涨 医疗,急救,医药,其他 H2请高手们不吝赐教~~感激不尽
wm_concat(d.teamname),
b.type
from table1 a,table2 b,table3 c,table4 d
where a.uid=b.uid
and a.uid=c.uid
and c.teamid=d.teamid
group by a.uid,a.uname,b.type
http://topic.csdn.net/u/20091010/14/FC7737C1-D60B-43F1-A8B5-A9EEF2DE4426.html
where t1.uid = t2.uid and t1.uid= t3.uid
and t3.teamid = t4.teamid
group by t1.uid,uname,t2.type
SELECT uid,uname,max(substr(sys_connect_by_path(teamname,','),2)) team,TYPE
FROM
(SELECT f.uid,
f.uname,
f.Teamname,
f.TYPE,
f.row_num,
Lag(f.Row_Num) Over(PARTITION BY f.uid, f.uname, f.TYPE ORDER BY uid) Row_Num1
FROM (SELECT d.uid, d.uname, d.Teamname, e.TYPE, Rownum Row_Num
FROM (SELECT a.uid, a.uname, c.Teamname
FROM Table1 a, Table3 b, Table4 c
WHERE a.uid = b.uid
AND b.teamID = c.teamID) d,
Table2 e
WHERE d.uid = e.uid) f) G
START WITH row_num1 IS NULL
CONNECT BY PRIOR row_num=row_num1
GROUP BY G.uid,G.uname,G.TYPE;