有两张表team(team_id,team_name)和team_member(team_member_id,team_member_name,team_id), team和team_member是通过team_id来关联的.
team_member里的数据是不固定的,对应每个team有1-6个成员.现在我要显示如下report, 想写一个view.该怎么写.数据库是oracle的. 多谢.
report格式如下:
team_name, team_member_name1, team_member_name1 .... team_member_name5, team_member_name6
team_member里的数据是不固定的,对应每个team有1-6个成员.现在我要显示如下report, 想写一个view.该怎么写.数据库是oracle的. 多谢.
report格式如下:
team_name, team_member_name1, team_member_name1 .... team_member_name5, team_member_name6
team_name, team_member_name1,team_member_name2....team_member_name5,team_member_name6
这个不知道怎么弄.
关键是我在sql里.怎么区分memeber1,member2
FROM TEAM A,
(SELECT TEAM_ID, WMSYS.WM_CONCAT(TEAM_MEMBER_NAME)
FROM TEAM_MEMBER
GROUP BY TEAM_ID) B
WHERE A.TEAM_ID = B.TEAM_ID
id varchar2(20)
)
create table bbb(
a_id varchar2(20),
a_name varchar2(20))
insert into aaa values('a');
insert into aaa values('b');
insert into bbb values('a', '1');
insert into bbb values('a', '2');
insert into bbb values('a', '3');
insert into bbb values('b', '1');
select id, wmsys.wm_concat(b.a_name) str
from aaa a, bbb b where a.id = b.a_id
group by id
,max(decode(rowindex,1,team_member_name,'')) as team_member_name1
,max(decode(rowindex,2,team_member_name,'')) as team_member_name2
,max(decode(rowindex,3,team_member_name,'')) as team_member_name3
,max(decode(rowindex,4,team_member_name,'')) as team_member_name4
,max(decode(rowindex,5,team_member_name,'')) as team_member_name5
,max(decode(rowindex,6,team_member_name,'')) as team_member_name6
from
(select tm.*,t.team_name from (
select team_member_id,team_member_name,team_id,
row_number over(patition by team_id order by team_member_id) as rowindex from t_team_member
)tm,team t where rowindex<=6 and tm/ream_id=t.teamid(+))v
group by team_id
但是我的需求其实更广泛一些。例如
team_member表里还存在更多字段,如 firstname, lastname, email,这些信息针对每个学生也是都要显示出来。
我想知道还有更简单的办法吗?
SELECT * FROM
( SELECT t.team_name,tm.team_member_name,DENSE_RANK() OVER (PARTITION BY tm.team_id ORDER BY tm.team_member_id) tmid
FROM team t,team_member tm WHERE t.team_id=tm.team_id )
PIVOT ( MAX(team_member_name) FOR tmid IN (1 team_member_name, 2 team_member_name2, 3 team_member_name3, 4 team_member_name4, 5 team_member_name5, 6 team_member_name6))
ORDER BY team_name;
参考
http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html