oracle SQL语句语法问题 ,在线等
例:
select zz as 字段,
(select xx from TBL1 where '条件') as 比例1,
(select yy from TBL2 where '条件') as 比例2
from TBL
where '联合条件'
现我想显示比例3,比例3=比例1*比例2
当我把比例3做为字段显示写SQL语句的时候写法很笨。
--//(select xx from TBL1 where '条件')*(select yy from TBL2 where '条件') as 比例3
请问有好的写法吗?
例:
select zz as 字段,
(select xx from TBL1 where '条件') as 比例1,
(select yy from TBL2 where '条件') as 比例2
from TBL
where '联合条件'
现我想显示比例3,比例3=比例1*比例2
当我把比例3做为字段显示写SQL语句的时候写法很笨。
--//(select xx from TBL1 where '条件')*(select yy from TBL2 where '条件') as 比例3
请问有好的写法吗?
--不行吗?select zz as 字段,
(select xx from TBL1 where '条件') as 比例1,
(select yy from TBL2 where '条件') as 比例2,
比例1 * 比例2 as 比例3
from TBL
where '联合条件';
SQL> select * from tbl; ID1 ID2
---------- ----------
1 2
2 3
5 7SQL> select id1,id2,id1*id2 from tbl; ID1 ID2 ID1*ID2
---------- ---------- ----------
1 2 2
2 3 6
5 7 35
(
select zz as 字段,
(select xx from TBL1 where '条件') as 比例1,
(select yy from TBL2 where '条件') as 比例2
from TBL
where '联合条件'
)
子查询效率低,建议改成Join.
TO:BLUESKYWID,当然不行,那是我AS的字段
TO:BUTCHROLLER,这个方法太笨,其实这条语句已经超过300行了
from TBL
left join TBL1 on '条件'
left join TBL2 on '条件'
where '联合条件'
(select sum(xx) from TBL1 where '条件') as 比例1,
(select sum(yy) from TBL2 where '条件') as 比例2
from TBL
where '联合条件'
结果:
Z 比例1 比例2
AAA 0.15 0.16
BBB 0.18 0.25
CCC 0.28 0.04显示比例3,比例3=比例1*比例2
SUM(XX)不能做为一个字段单独拿出来,也不能再用外层
select 比例1,比例2,比例1*比例2 as 比例3 from (...)