现在通过查询构造了两张虚表，数据直接没有关系，我通过建立一张关系表，要把表A和表B的数据实现拼接
例如A表数据
aid signname data
1 指标1 11
2 指标2 21
表B
bid signname data
3 指标3 31
4 指标4 41
关系表
id aid bid
1 1 3
2 2 4
我的sql语句应该怎么写？值得注意的是我的a和b表都是sql语句查询出来的，所以本来sql语句就已经很复杂了
希望高手多多指点，谢谢
例如A表数据
aid signname data
1 指标1 11
2 指标2 21
表B
bid signname data
3 指标3 31
4 指标4 41
关系表
id aid bid
1 1 3
2 2 4
我的sql语句应该怎么写？值得注意的是我的a和b表都是sql语句查询出来的，所以本来sql语句就已经很复杂了
希望高手多多指点，谢谢
--如果你的a表和b表都是很复杂的语句查询结果的话
--可以将查询结果生成临时表a和b
with a as(
select 1 aid,'指标1' signname,11 data from dual union all
select 2, '指标2', 21 from dual),
b as(
select 3 bid,'指标3' signname,31 data from dual union all
select 4, '指标4', 41 from dual)
select aa.rn,aa.aid,bb.bid
from (select rownum rn,a.* from a) aa,
(select rownum rn,b.* from b) bb
where aa.rn=bb.rn
RN AID BID
---------- ---------- ----------
1 1 3
2 2 4
row_number() over(partition by aid order by aid) rn
row_number() over(partition by bid order by bid) rn然后在a.rn=b.rn
gqrs.id,
gqrs.vsignid_jszb,
gqrs.vsignid_cl,
gqrs.signname,
jszb.isdata,
jszb.isoutput,
jszb.signorg,
jszb.unitname,
jszb.issubmit,
jszb.cropn,
jszb.c0n c0n_jszb, --技术指标当月
jszb.c0nyc c0nyc_jszb, --技术指标去年同期累计
cl.signname signname_cl,
cl.c0n c0n_cl, --产量本月
cl.c0nyb c0nyb_cl --产量去年本月
from getqueryrelationship gqrs --关系表
left join
(
select
crs.isdata
,(case when sum(nvl(crs.cumulative,0)) > 0 or crs.isdata = 0 then 1 else 0 end) isoutput
,crs.vsignid
,crs.signname signorg /*指标原始名称*/
,crs.unitname /*计算单位名称*/
,crs.coefficient /*计算单位系数*/
,case crs.cropid when 0 then 0 else 1 end issubmit
,crs.cropn /*指标*/
,sum(case when crs.year = 2010 and crs.month = 11 and crs.hsignid = 10003 then crs.data else 0 end) c0n /*指标当月*/
,sum(case when crs.year = 2009 and crs.month = 11 and crs.hsignid = 10003 then crs.cumulative else 0 end) c0nyc /*指标去年同期累计*/
from
( /*指标项目数据汇总*/
select
reportid
,reportcode
,cropid
,cropn
,i_gsxz
,i_gsfl
,i_xh
,year
,month
,vsignid
,10003 hsignid
,signname
,isdata
,unitname
,numerator
,denominator
,coefficient
,(case when sum(case when hsignid = 10005 then data else 0 end) > 0
then sum(case when hsignid = 10004 then data else 0 end)/sum(case when hsignid = 10005 then data else 0 end) * coefficient
else 0 end) data
,'''' /**/
,(case when sum(case when hsignid = 10005 then cumulative else 0 end) > 0
then sum(case when hsignid = 10004 then cumulative else 0 end)/sum(case when hsignid = 10005 then cumulative else 0 end)* coefficient
else 0 end) cumulative
,''''
from pis.reportdata_sum_view
where reportid = 2
and i_gsxz in (0,1) /*按照公司性质过滤数据*/
and hsignid in (10004,10005)
and ( (year between 2009 and 2010) and month = 11)
group by
reportid
,reportcode
,cropid
,cropn
,i_gsxz
,i_gsfl
,i_xh
,year
,month
,vsignid
,signname
,parentid
,isdata
,unitname
,numerator
,denominator
,coefficient
) crs
JOIN (select * from reportsign_tree_view where reportid = 2) rtv /*报表排序*/
ON crs.reportid = rtv.reportid
AND crs.vsignid = rtv.signid
/*特殊排序数据*/
LEFT JOIN ( select signid,parentid,level as l
from (select * from pis.reportsign
where reportsign.reportid = 2 )
start with parentid = 90
connect by prior signid = parentid) rs
ON rs.l = 2 and crs.vsignid = rs.signid
where (case when nvl(rs.signid ,0) = 0 then 1 else cropid end ) > 0
and crs.vsignid in (11031,11033,11034,11042,11046,11045,11515,11036,10693)
and crs.cropn <> '其中:中国铝业'
group by
rs.signid
,crs.isdata
,crs.vsignid
,crs.signname
,crs.unitname
,crs.coefficient
,crs.cropid
,crs.i_gsfl
,crs.cropn
,crs.i_xh
order by crs.i_xh
) jszb ---能耗技术指标表
on gqrs.vsignid_jszb=jszb.vsignid
left join
(
select
rdv.isdata,
(case when nvl(sum(rdv.cumulative),0) > 0 or rdv.isdata = 0 then 1 else 0 end ) isoutput, --出否输出
rdv.vsignid, --指标id
rdv.signname signname,
rdv.unitname,
rdv.coefficient, --系数 reportsdata_view
0 issubmit, --设置为0表示不显示文本框，即不提交
nvl(sum(rdv.data),0) c0n, --本月
nvl(sum(rdvy.data),0) c0nyb --去年本月
from reportsdata_view rdv
join reportsign_tree_view rtv
on rdv.reportid = rtv.reportid
and rdv.vsignid = rtv.signid
left join reportsdata_view rdvy --去年
on rdv.cropid = rdvy.cropid
and rdv.reportid = rdvy.reportid
and rdv.vsignid = rdvy.vsignid
and rdv.hsignid = rdvy.hsignid
and rdvy.year = 2009--p_year-1
and rdvy.month = 9--p_month
where rdv.reportid = 11--p_reportid
and rdv.year = 2010--p_year
and rdv.month = 9--p_month
and rdv.hsignid = 10002
and rdv.i_gsxz in (0,1)--(v_gsxz0,v_gsxz1) --上市或未上市
and rdv.cropid not in (select iid --除去不合计的公司
from system_argument
where vname='nosum')
and rdv.vsignid in(749,10453,773,10870,806,10905,10381,776,860,808,11019,772,10643)
group by rtv.rn, --行数
rtv.indent, --缩进
rdv.coefficient, --系数
rdv.year,
rdv.month,
rdv.reportid,
rdv.vsignid, --指标id
rdv.hsignid,
rdv.isdata,
rdv.parentid,
rdv.signname,
rdv.unitname
--指标选择sql语句结束
order by rn
) cl ---产量表
on gqrs.vsignid_cl = cl.vsignid
order by id
http://topic.csdn.net/u/20101228/14/7c49ec10-1158-4071-ac3f-f80b93da5069.html
(select t.* , row_number() over(order by aid) px from a t) m
full join
(select t.* , row_number() over(order by bid) px from b t) n
on m.px = n.px其中的a , b可以做为你的查询带进去.
该SQL的性能和可维护性都很差，如果以后要修改功能了，那时就要头疼了。
建议通过建立临时表，将中间过程记录到临时表中，达到使用空间换时间的目的，提升性能和可维护性。