create view v1 select t1.username,t1.usernumber,convert(sum(t2.userscore)-abs(sum(t2.userscore)),0,0,abs(sum(t2.userscore))),convert(sum(t2.userscore)-abs(sum(t2.userscore)),0,sum(t2.userscore),0) from t1,t2 group by t1.username,t1.usernumber
调试欢乐多
select c.UserNumber,c.UserName,a.UserCount,b.UserTotal
from
(select UserNumber,sum(UserScore) as UserCount
from Table2
group by UserNumber
) a,
(select UserNumber,sum(UserScore) as UserTotal
from Table2
where UserScore>0
group by UserNumber
) b,
Table1 c
where c.UserNumber=a.UserNumber and c.UserNumber=b.UserNumber
(select usernumber,sum(userscore) usercount,sum(decode(sign(usercode),-1,0,usercode)) usertotal
from table2 group by usernumber)a,table1 b
where a.usernumber=b.username(+)
你的写法确实比较好,不过有点小错误。我帮你改改啊create or replace view v_Account as
select a.usernumber, b.username,a.usercount,a.usertotal
from
(select usernumber,sum(userscore) usercount,sum(decode(sign(usercode),-1,0,usercode)) usertotal
from table2
group by usernumber
) a,table1 b
where a.usernumber=b.usernumber(+)