Account表
name money
a 100
b 0
c 100
------------------------
Detail表
name type money
a 存款 200
a 取款 -200
a 存款 100查询出来的结果要这样用户 余额 取款 存款
a 100 -200 200
b 0 0 0
c 100 0 0先感谢各位帮忙!
name money
a 100
b 0
c 100
------------------------
Detail表
name type money
a 存款 200
a 取款 -200
a 存款 100查询出来的结果要这样用户 余额 取款 存款
a 100 -200 200
b 0 0 0
c 100 0 0先感谢各位帮忙!
献丑了
a 100 -200 300
b 0 0 0
c 100 0 0 修改下`
可以交流
select 字查询用不起 from ....
COALESCE((SELECT SUM(MONEY) FROM CLASSICCARS.DETAIL D WHERE D.NAME=A.NAME AND D.TYPE='取款'),0) AS "取款",
COALESCE((SELECT SUM(MONEY) FROM CLASSICCARS.DETAIL E WHERE E.NAME=A.NAME AND E.TYPE='存款'),0) AS "存款"
from CLASSICCARS.ACCOUNT A ;COALESCE 换成 NVL也可以的。手头没有数据库写SQL真是吃力啊,写完还得多看几遍,应该没错吧
你在oracle里试试看吧,schema去掉。下面那个为准。
select A.NAME AS "用户",A.MONEY AS "余额",
COALESCE((SELECT SUM(MONEY) FROM DETAIL D WHERE D.NAME=A.NAME AND D.TYPE='取款'),0) AS "取款",
COALESCE((SELECT SUM(MONEY) FROM DETAIL E WHERE E.NAME=A.NAME AND E.TYPE='存款'),0) AS "存款"
from ACCOUNT A ;
你这样可以``如果type类型多了效率就不太高了`但是还是感谢你的帮助分给你了`谢谢`