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)
(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)
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
(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
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
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(+)
select
(select sum(amount) from accountTable where userid=user.userid and type='income') as income
即把子查询写在select部分的能执行成功吗?
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;
E:\oracle\ora92\database>sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on 星期五 1月 25 11:22:16 2002Copyright (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 morningSQL> 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 morningSQL>