表A
enterprisename sbgqhj
企业一 100
企业二 150
企业三 120表B
enterprisename sjqhj
企业一 25
企业二 45
企业四 100现在要的结果
enterprisename sbgqhj sjqhj
企业一 100 25
企业二 150 45
企业三 120 0
企业四 0 100
这样的SQL语句该怎么写(数据比较多)我现在有的执行速度太慢
现在的语句是:
select * from (select a.enterprisename,nvl(b.sjqhj,0) as sjqhj,nvl(a.sbgqhj,0) as sbgqhj from
a left outer join b on a.enterprisename=b.enterprisename union
select b.enterprisename,nvl(b.sjqhj,0) as sjqhj,nvl(a.sbgqhj,0) as sbgqhj from
a right outer join b on a.enterprisename=b.enterprisename) t
order by sbgqhj desc
enterprisename sbgqhj
企业一 100
企业二 150
企业三 120表B
enterprisename sjqhj
企业一 25
企业二 45
企业四 100现在要的结果
enterprisename sbgqhj sjqhj
企业一 100 25
企业二 150 45
企业三 120 0
企业四 0 100
这样的SQL语句该怎么写(数据比较多)我现在有的执行速度太慢
现在的语句是:
select * from (select a.enterprisename,nvl(b.sjqhj,0) as sjqhj,nvl(a.sbgqhj,0) as sbgqhj from
a left outer join b on a.enterprisename=b.enterprisename union
select b.enterprisename,nvl(b.sjqhj,0) as sjqhj,nvl(a.sbgqhj,0) as sbgqhj from
a right outer join b on a.enterprisename=b.enterprisename) t
order by sbgqhj desc
from
(select enterprisename,sbgqhj,0 sjqhj from A
union all select enterprisename, 0 sbgqhj,sjqhj from A
)
group by enterprisename
from
(select enterprisename,sbgqhj,0 sjqhj from A
union all select enterprisename, 0 sbgqhj,sjqhj from B
)
group by enterprisename
FROM (SELECT A.ENTERPRISENAME,
NVL(B.SJQHJ, 0) AS SJQHJ,
NVL(A.SBGQHJ, 0) AS SBGQHJ
FROM A
FULL OUTER JOIN B ON A.ENTERPRISENAME = B.ENTERPRISENAME) T
ORDER BY SBGQHJ DESC;
不知道full outer join在9i会不会出现Bug问题了?
union all
select b.enterprisename , nvl(a.sbgqhj , 0) sbgqhj , b.sjqhj from a right join b on a.enterprisename = b.enterprisename
nvl(a.sbgqhj , 0) sbgqhj,
nvl(b.sjqhj , 0) sjqhj
from a full join b on a.enterprisename = b.enterprisename
order by a.enterprisename
FROM (SELECT A.ENTERPRISENAME,
NVL(B.SJQHJ, 0) AS SJQHJ,
NVL(A.SBGQHJ, 0) AS SBGQHJ
FROM A
FULL OUTER JOIN B ON A.ENTERPRISENAME = B.ENTERPRISENAME) T
ORDER BY SBGQHJ DESC;
from
(select enterprisename,sbgqhj,0 sjqhj from A
union all select enterprisename, 0 sbgqhj,sjqhj from B
)
group by enterprisename
学习了,就用这个就行,解决了B表看不到的问题 (NVL函数)
你中间不比union啊,你重复了,明白吗?
以后sql语句要格式化,有助于查错,OK!
原语句如下:
select *
from(
select a.enterprisename,nvl(b.sjqhj,0) as sjqhj,nvl(a.sbgqhj,0) as sbgqhj
from a
left outer join b
on a.enterprisename=b.enterprisename
union
select b.enterprisename, nvl(b.sjqhj,0) as sjqhj, nvl(a.sbgqhj,0) as sbgqhj
from a
right outer join b
on a.enterprisename=b.enterprisename
) t
order by sbgqhj desc
看到,子查询里面的union是多余的吧,修改后的语句如下:
select *
from(
select a.enterprisename,nvl(b.sjqhj,0) as sjqhj,nvl(a.sbgqhj,0) as sbgqhj
from a
left outer join b
on a.enterprisename=b.enterprisename
) t
order by sbgqhj desc
再次修改如下: select a.enterprisename,nvl(b.sjqhj,0) as sjqhj,nvl(a.sbgqhj,0) as sbgqhj
from a
left outer join b
on a.enterprisename=b.enterprisename
order by sbgqhj desc
sum(case when c.tablename='A' then c.t2 else 0 end) as sbgqhj,
sum(case when c.tablename='B' then c.t2 else 0 end) as sjqhj,
select enterprisename as t1,sbgqhj as t2,'A' as tablename from A
union all
select enterprisename as t1,sjqhj as t2,'B' as tablename from B
) c group by c.t1