子查询的结果能否再次引用？

[emu]

我在一个查询里面使用到了两个子查询，并且还需要这两个子查询的结果之差，不知怎么写？

我原来的想法是象这样：
select
(select sum(amount) from accountTable where userid=user.userid and type='income') as income,
(select sum(amount) from accountTable where userid=user.userid and type='payout') as payout,
(income-payout) as balance
from user

但是在(income-payout) as balance这里出了错，好像不能在查询里面再次引用子查询的结果，我又不想再多做一次子查询，请问该怎么办？

请注意，两个子查询是根据不同条件查同一个表的，似乎不能用groupby来代替？

[emu]

呵呵，这不就是？：表达式吗？我还一直发愁在sql里面写不出来问号表达式呢。非常感谢。这是个新问题，我另外开个帖子给您分吧。

[lunar2000]

在oracle中实现if...else...的功能，除了decode外，还有一个case比较方便，呵呵：
E:\oracle\ora92\database>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 1月 25 11:22:16 2002

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn lunar/lunar
已连接。
SQL> create table testcase
2 (a char(6),
3 b number(10),
4 c char(9),
5 d number(10,2),
6 e char(20));

表已创建。

SQL> insert into testcase values('aa',110,'你好',100.2,'abc');

已创建 1 行。

SQL> insert into testcase values('bb',200,'大家好',120.25,'abcde');

已创建 1 行。

SQL> insert into testcase values('cc',150,'和平',50.9,'xyz');

已创建 1 行。

SQL> insert into testcase values('dd',170,'和平',200,'rst');

已创建 1 行。

SQL> insert into testcase values('ee',10,'公安部',21,'morning');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from testcase;

A B C D E
------ ---------- --------- ---------- --------------------
aa 110 你好 100.2 abc
bb 200 大家好 120.25 abcde
cc 150 和平 50.9 xyz
dd 170 和平 200 rst
ee 10 公安部 21 morning

SQL> select a,b,(case when b>150 then '大于150'
2 when b=150 then '等于150'
3 when b<150 then '小于150'
4 else '输入错误'
5 end) as c, d,e
6 from testcase
7 /

A B C D E
------ ---------- -------- ---------- --------------------
aa 110 小于150 100.2 abc
bb 200 大于150 120.25 abcde
cc 150 等于150 50.9 xyz
dd 170 大于150 200 rst
ee 10 小于150 21 morning

SQL>

[bzszp]

select income,payout,(income-payout) balance from (select
(select sum(amount) from accountTable where userid=user.userid and type='income') as income,
(select sum(amount) from accountTable where userid=user.userid and type='payout') as payout
from user)

[supershb]

select T.income , T.payout , (T.income - T.payout) as balance
from ( select
(select sum(amount) from accountTable where userid=user.userid and type='income') as income,
(select sum(amount) from accountTable where userid=user.userid and type='payout') as payout
from user) T

[jiezhi]

select
(select sum(amount) from accountTable where userid=user.userid and type='income') as income,
(select sum(amount) from accountTable where userid=user.userid and type='payout') as payout,
((select sum(amount) from accountTable where userid=user.userid and type='income')-(select sum(amount) from accountTable where userid=user.userid and type='payout')) as balance
from user

[Lastdrop]

利用子查询的方法：
select c.userid,a.income, b.payout, (a.income-b.payout) as balance
from
( select userid, sum(amount) income from accountTable where
type='income' group by userid) a ,
(select userid,sum(amount) payout from accountTable where
type='payout' group by userid) b,
user c
where a.userid=c.userid and b.userid= c.userid

也可以利用decode方法
select c.userid,sum(decode(a.type,'income',a.amount,0)) income,
sum(decode(a.type,'payout',a.amount,0)) payout ,
sum(decode(a.type,'income',a.amount,-a.amount)) balance
from accountTable a, user c
where a.userid=c.userid
group by c.userid

[emu]

高手真多 :-P

以前他们说，到了深圳才知道自己穷，到了北京才知道自己官小。现在我觉得，到了csdn才知道自己菜。

[beckhambobo]

select sum(b.amount) income,sum(c.amount) payout,(sum(b.amount)-sum(c.amount)) balance
from user a,(select amount from accountTable where type='income') b,(select amount from accountTable where type='payout') c
where a.userid=b.userid(+) and a.userid=b.userid(+)

[Lastdrop]

请问楼主，这些不同的SQL你都试过了吗？类似
select
(select sum(amount) from accountTable where userid=user.userid and type='income') as income
即把子查询写在select部分的能执行成功吗？

[emu]

谢谢数据库版的兄弟姐妹们帮忙了！

[beckhambobo]

8i以上可以执行成功

[emu]

Lastdrop(空杯) :我试过的，当然实际的sql并不完全象这样。您提供的第一种办法非常好，用临时表代替了反复的子查询，查询结果很多的时候应该有很大的效率优势吧？decode函数我还不懂，手边没有手册，能给我讲讲吗？

[Lastdrop]

decode函数相当于if then elsif then else
decode(a.type,'income',a.amount,0)的意思是
if a.type = 'income' then
此时取a.amount的值作为decode函数的返回值
else
以0 作为decode函数的返回值
end if;
当然，decode还可以有更多的条件
decode(a.type,'income',a.amount,'payout',-a.amount,0)
if a.type = 'income' then
此时取a.amount的值作为decode函数的返回值
elsif a.type = 'payout' then
此时取 -a.amount的值作为decode函数的返回值
else
以0 作为decode函数的返回值
end if;