如题,要求把嵌套换成表连接。
以下是SQL语句。
select b.fileid, b.contentid,b.filepath,b.filetype,b.transferstatus,b.usagecode,b.codeformat,
b.coderate, b.nettype,b.playurl,b.destpath
from (select t.fileid, t.contentid, t.filepath, t.filetype,
i_transferstatus transferstatus, i_usagecode usagecode, t.codeformat, i_coderate coderate,
t.nettype, t.playurl, l_str_destpath destpath
from t_cms_mtv_file@dblink_cms t
where exists (select 1
from t_cms_mtv@dblink_cms a
where t.contentid = a.contentid
and a.type = i_type
)
and t.filetype = i_filetype_old
and t.codeformat = i_codeformat_old
and t.coderate = i_coderate_old
and t.nettype = i_nettype_old
)b
where exists (select 1
from t_cms_content_tag@dblink_cms c, t_cms_tag@dblink_cms d
where b.contentid = c.contentid
and c.tagid = d.tagid
and d.tagname like '%'||str_tagname||'%');
以下是SQL语句。
select b.fileid, b.contentid,b.filepath,b.filetype,b.transferstatus,b.usagecode,b.codeformat,
b.coderate, b.nettype,b.playurl,b.destpath
from (select t.fileid, t.contentid, t.filepath, t.filetype,
i_transferstatus transferstatus, i_usagecode usagecode, t.codeformat, i_coderate coderate,
t.nettype, t.playurl, l_str_destpath destpath
from t_cms_mtv_file@dblink_cms t
where exists (select 1
from t_cms_mtv@dblink_cms a
where t.contentid = a.contentid
and a.type = i_type
)
and t.filetype = i_filetype_old
and t.codeformat = i_codeformat_old
and t.coderate = i_coderate_old
and t.nettype = i_nettype_old
)b
where exists (select 1
from t_cms_content_tag@dblink_cms c, t_cms_tag@dblink_cms d
where b.contentid = c.contentid
and c.tagid = d.tagid
and d.tagname like '%'||str_tagname||'%');
(select b.fileid, b.contentid,b.filepath,b.filetype,b.transferstatus,b.usagecode,b.codeformat,
b.coderate, b.nettype,b.playurl,b.destpath from
(select t.fileid,t.contentid,t.filepath,t.filetype,
1 transferstatus,3 usagecode,t.codeformat,15 coderate ,t.nettype,t.playurl, null destpath
from t_cms_mtv_file@dblink_cms t, t_cms_mtv@dblink_cms a
where t.filetype = 2
and t.codeformat = '7'
and t.coderate = '30'
and t.nettype = '3'
and t.contentid = a.contentid
and a.type = 4) b, t_cms_content_tag@dblink_cms c, t_cms_tag@dblink_cms d
where b.contentid = c.contentid
and c.tagid = d.tagid
and d.tagname like '%客户端类节目%');
1 transferstatus,3 usagecode,t.codeformat,15 coderate ,t.nettype,t.playurl, null destpath
from t_cms_mtv_file@dblink_cms t,
t_cms_mtv@dblink_cms a,
t_cms_content_tag@dblink_cms c,
t_cms_tag@dblink_cms d
where t.contentid = c.contentid
and t.contentid = a.contentid
and c.tagid = d.tagid
and d.tagname like '%客户端类节目%'
and t.filetype = 2
and t.codeformat = '7'
and t.coderate = '30'
and t.nettype = '3'
and a.type = 4;
貌似效率还要高点!呵呵!
select t.fileid,
t.contentid,
t.filepath,
t.filetype,
1 transferstatus,
2 usagecode,
t.codeformat,
10 coderate,
t.nettype,
t.playurl,
null destpath
from t_cms_mtv_file@dblink_cms t
where exists(select 1 from t_cms_mtv@dblink_cms a,t_cms_content_tag@dblink_cms b,t_cms_tag@dblink_cms c
where a.type = 4
and t.contentid = a.contentid
and t.contentid = b.contentid
and b.tagid = c.tagid
and c.tagname like '%WAP类节目%'
)
and t.filetype = 2
and t.codeformat = 7
and t.coderate = 30
and t.nettype = 3