请教一个简单SQL的语法

[kang_krs]

SELECT AVG(A) FROM (SELECT ZQQ_WD_JL_J A FROM YX_LY_SSHLYXRZ_ZYCS WHERE NVL(ZQQ_WD_JL_J,0)!=0 UNION ALL SELECT ZQQ_WD_JL_Y A FROM YX_LY_SSHLYXRZ_ZYCS WHERE NVL(ZQQ_WD_JL_Y,0)!=0);

报错ORA-00904: invalid column name

请问这SQL怎么定义列的别名

[BlueskyWide]

SELECT (tab1.a1+tab2.b1)/(tab1.a2+tab2.b2) AVG_test FROM (SELECT sum(ZQQ_WD_JL_J) A1,count(ZQQ_WD_JL_J) a2 FROM YX_LY_SSHLYXRZ_ZYCS WHERE NVL(ZQQ_WD_JL_J,0)!=0) tab1,(SELECT sum(ZQQ_WD_JL_Y) B1,count(ZQQ_WD_JL_Y) b2 FROM YX_LY_SSHLYXRZ_ZYCS WHERE NVL(ZQQ_WD_JL_Y,0)!=0) tab2;

[beckhambobo]

SQL> select * from aaa;

NAME NUM
---------- ----------
1

SQL> select avg(a) from (select num a from aaa union all select num a from aaa);

AVG(A)
----------
1

无什么问题，细心检查程序那里出错

[sunnf]

SELECT AVG(aa.A) FROM (SELECT ZQQ_WD_JL_J A FROM YX_LY_SSHLYXRZ_ZYCS WHERE NVL(ZQQ_WD_JL_J,0)!=0 UNION ALL SELECT ZQQ_WD_JL_Y A FROM YX_LY_SSHLYXRZ_ZYCS WHERE NVL(ZQQ_WD_JL_Y,0)!=0) aa;

你试一试，我没有测试 。