今天开发接了一个很BT的需求。
?找一个人的所有好友，查询所有好友的所有作品，然后按照时间倒序排列,取若干记录，
?然后关联作品评论表。
?
作品包括原唱表，翻唱表，伴奏表，视频表，博客表和照片表,
?不同的作品类型还要关联不同的专辑表，最后还要关联用户表..
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
结果就是这个SQL很长...
?with
?t1 as (select to_userid from friend_list f where f.userid=411602438),
?t2 as (
? select 'mc' as t,rid,createtime
? from
? (
? ? select mc.rowid rid,mc.createtime from music_cover mc,t1 where mc.userid=t1.to_userid and mc.opus_stat >0 order by mc.createtime desc
? ) where rownum< 100
? union all
? select 'mo',rid,createtime
? from
? (
? ? select mo.rowid rid,mo.createtime? from music_original mo,t1 where mo.userid=t1.to_userid and mo.opus_stat >0 order by mo.createtime desc
? ) where rownum< 100
?
? union all
? select 'mv',rid,createtime
? from
? (
? ? select mv.rowid rid,mv.createtime? from music_video mv,t1 where mv.userid=t1.to_userid and mv.opus_stat >0 order by mv.createtime desc
? ) where rownum< 100
?
? union all
? select 'ma',rid,createtime
? from
? (
? ? select ma.rowid rid,ma.createtime from music_accompany ma,t1 where ma.userid=t1.to_userid and ma.opus_stat >0 order by ma.createtime desc
? ) where rownum< 100
?
? union all
? select 'bl',rid,createtime
? from
? (
? ? select bl.rowid rid,bl.createtime? from blog_list bl,t1 where bl.userid=t1.to_userid and bl.opus_stat >0 order by bl.createtime desc
? ) where rownum< 100
?
? union all
? select 'pl',rid,createtime
? from
? (
? ) where rownum< 100
?),
?t3 as
?(
? select * from
? (
? ? select * from t2 order by createtime desc
? )
? where rownum<100
?),
?t4 as
?(
?select
? t3.t,
? decode(t3.t,
?'mc',2,
?'mo',2,
?'mv',2,
?'ma',2,
?'pl',4,
?'bl',5
?) type_code,
? mc.userid||mo.userid||mv.userid||ma.userid||bl.userid||pl.userid userid,
? mc.file_url||mo.file_url||mv.file_url||ma.file_url||bl.file_url||pl.file_url file_url,
? mc.opus_Name||mo.opus_Name||mv.opus_name||ma.opus_name||bl.opus_name||pl.opus_name opus_name,
? mc.opus_id||mo.opus_id||mv.opus_id||ma.opus_id||bl.opus_id||pl.opus_id opus_id,
? mc.createtime||mo.createtime||mv.createtime||ma.createtime||bl.createtime||pl.createtime createtime,
? mv.opus_desc||mo.opus_desc||mc.opus_desc||ma.opus_desc||bl.opus_desc||pl.opus_desc opus_desc,
? mv.album_id||mo.album_id||mc.album_id||ma.album_id||bl.album_id||pl.album_id album_id,
? mv.visit_num||mo.visit_num||mc.visit_num||ma.visit_num||bl.visit_num||pl.visit_num visit_num
?from t3
?left join music_cover mc on(t3.rid=mc.rowid)
?left join music_original mo on(t3.rid=mo.rowid)
?left join music_video mv on(t3.rid=mv.rowid)
?left join music_accompany ma on(t3.rid=ma.rowid)
?left join blog_list bl on(t3.rid=bl.rowid)
?left join photo_list pl on(t3.rid=pl.rowid)
?)
?select /*+ ordered use_nl(t4,base) */
?base.nickname,
?decode(t4.type_code,
?2,(select al.album_name from music_album al where al.album_id=t4.album_id),
?4,(select al.album_name from photo_album al where al.album_id=t4.album_id),
?5,(select al.album_name from blog_album al where al.album_id=t4.album_id)
?) album_name,
?(select count(*) from user_comment com where com.typeid=t4.type_code and t4.opus_id=com.to_id and status=1) commentTotal,
?t4.*
?from t4,mvbox_user.user_basein