也就是说,这样的with deps_tmp as(select depsinfo.*,row_number()over(partition by appid order by rowid) rn from depsinfo), BSA_tmp as(select BSAINFO.*,row_number()over(partition by appid order by rowid)rn from BSAINFO) select nvl(t1.appid,t2.appid) appid,nvl(t1.depsrow,0),nvl(t1.depsmoney,0),nvl(t2.bsarow,0),nvl(t2.bsamoney,0) from deps_tmp t1 full join bsa_tmp t2 on t1.appid=t2.appid and t1.rn=t2.rn order by nvl(t1.appid,t2.appid),t1.rn nulls last,t2.rn nulls last
两个表根据appid 和row_number 外连接
大版速度真快,我提供一个其他的写法吧,开拓下思路,呵呵 select appid,sum(depsRow) depsRow,sum(depsMoney) depsMoney,sum(bsaRow) bsaRow,sum(bsaMoney) bsaMoney from ( select appid,depsRow,depsMoney,0 as bsaRow,0 as bsaMoney, row_number()over(partition by appid order by rowid) rn from depsinfo union all select appid,0 as depsRow,0 as depsMoney,bsaRow,bsaMoney, row_number()over(partition by appid order by rowid)rn from BSAINFO )group by appid,rn order by appid,rn
SELECT a.appid, NVL (depsrow, 0), NVL (depsmoney, 0), NVL (bsarow, 0), NVL (bsamoney, 0) FROM (SELECT d.*, ROW_NUMBER () OVER (PARTITION BY appid ORDER BY depsrow DESC) rn FROM depsInfo d) a FULL JOIN (SELECT bs.*, ROW_NUMBER () OVER (PARTITION BY appid ORDER BY bsarow DESC) rn FROM bsaInfo bs) b ON a.appid = b.appid AND a.rn = b.rn
SELECT case when a.appid is null then b.appid else a.appid end appid, NVL (depsrow, 0), NVL (depsmoney, 0), NVL (bsarow, 0), NVL (bsamoney, 0) FROM (SELECT d.*, ROW_NUMBER () OVER (PARTITION BY appid ORDER BY depsrow DESC) rn FROM depsInfo d) a FULL JOIN (SELECT bs.*, ROW_NUMBER () OVER (PARTITION BY appid ORDER BY bsarow DESC) rn FROM bsaInfo bs) b ON a.appid = b.appid AND a.rn = b.rn order by 1
BSA_tmp as(select BSAINFO.*,row_number()over(partition by appid order by rowid)rn from BSAINFO)
select nvl(t1.appid,t2.appid) appid,nvl(t1.depsrow,0),nvl(t1.depsmoney,0),nvl(t2.bsarow,0),nvl(t2.bsamoney,0)
from deps_tmp t1 full join bsa_tmp t2
on t1.appid=t2.appid
and t1.rn=t2.rn
order by nvl(t1.appid,t2.appid),t1.rn nulls last,t2.rn nulls last
select appid,sum(depsRow) depsRow,sum(depsMoney) depsMoney,sum(bsaRow) bsaRow,sum(bsaMoney) bsaMoney from (
select appid,depsRow,depsMoney,0 as bsaRow,0 as bsaMoney,
row_number()over(partition by appid order by rowid) rn from depsinfo
union all
select appid,0 as depsRow,0 as depsMoney,bsaRow,bsaMoney,
row_number()over(partition by appid order by rowid)rn from BSAINFO
)group by appid,rn order by appid,rn
SELECT a.appid,
NVL (depsrow, 0),
NVL (depsmoney, 0),
NVL (bsarow, 0),
NVL (bsamoney, 0)
FROM (SELECT d.*,
ROW_NUMBER ()
OVER (PARTITION BY appid ORDER BY depsrow DESC)
rn
FROM depsInfo d) a
FULL JOIN
(SELECT bs.*,
ROW_NUMBER ()
OVER (PARTITION BY appid ORDER BY bsarow DESC)
rn
FROM bsaInfo bs) b
ON a.appid = b.appid AND a.rn = b.rn
NVL (depsrow, 0),
NVL (depsmoney, 0),
NVL (bsarow, 0),
NVL (bsamoney, 0)
FROM (SELECT d.*,
ROW_NUMBER ()
OVER (PARTITION BY appid ORDER BY depsrow DESC)
rn
FROM depsInfo d) a
FULL JOIN
(SELECT bs.*,
ROW_NUMBER ()
OVER (PARTITION BY appid ORDER BY bsarow DESC)
rn
FROM bsaInfo bs) b
ON a.appid = b.appid AND a.rn = b.rn
order by 1
除了APPID 他们还有一种方式关联,是通过另外一张表
sh_merchant表
有三个关键字段字段APPID merchantcode dz_flag,其他无关字段就不写出了
merchantcode 这个字段也存在于bsaInf表中。我只是想问问各位资历比较深的大神,不用第三张表能不能实现这一个结果。如果要关联第三张表sh_merchant非常麻烦,这里面的数据和bsaInfo也是多对一的关系。
目前看来 不需要第三张表也实现了这个功能撒。
其实这三张表包含了特定的业务关系,是不可忽略的,即使这里的sql语句能处理楼主提供的的示例数据,但也不尽然是完善的。
所以建议楼主还是业务逻辑出发,不要图省事啊