初学者求一SQL语句，办法都想尽了就是想不出来 ( 积分: 50 )

有A、B两张表，A表表示存款明细表，B表表示取款明细表，现我要根据这两张表的明细算出每个卡号的帐户余额用下面这个语句能实现，但得出的结果是没有办法显示‘帐户余额’这个字段，用AS帐户余额是不行的，请问怎么做能显示出‘帐户余额’<br>select&nbsp;a.卡号,sum(a.存入金额)-(select&nbsp;sum(b.取出金额)&nbsp;from&nbsp;b&nbsp;where&nbsp;a.卡号=b.卡号)&nbsp;from&nbsp;a&nbsp;group&nbsp;by&nbsp;a.卡号<br>&nbsp;如：select&nbsp;a.卡号,(sum(a.存入金额)-(select&nbsp;sum(b.取出金额)&nbsp;from&nbsp;b&nbsp;where&nbsp;a.卡号=b.卡号))&nbsp;as&nbsp;帐户余额&nbsp;from&nbsp;a&nbsp;group&nbsp;by&nbsp;a.卡号&nbsp;这样同样不行。

select&nbsp;a.卡号,(a.金额-b.金额)&nbsp;金额&nbsp;from<br>(select&nbsp;a.卡号,sum(a.存入金额)&nbsp;金额&nbsp;from&nbsp;a&nbsp;group&nbsp;by&nbsp;a.卡号)&nbsp;a,<br>(select&nbsp;b.卡号,sum(b.取出金额)&nbsp;金额&nbsp;from&nbsp;b&nbsp;group&nbsp;by&nbsp;b.卡号)&nbsp;b<br>where&nbsp;a.卡号=b.卡号<br><br>这样行吗？

呵呵，没看出什么问题来，你试一下<br>select&nbsp;a.卡号&nbsp;as&nbsp;卡号,(sum(a.存入金额)-(select&nbsp;sum(b.取出金额)&nbsp;from&nbsp;b&nbsp;where&nbsp;a.卡号=b.卡号))&nbsp;as&nbsp;帐户余额&nbsp;from&nbsp;a&nbsp;group&nbsp;by&nbsp;a.卡号

提示‘AS帐户余额’有问题，应该是不能这样加的，但除了这种方式不知道还能用什么方式才能显示‘帐户余额’

Select&nbsp;卡号,&nbsp;Sum(金额)&nbsp;As&nbsp;帐户余额&nbsp;From&nbsp;<br>(Select&nbsp;卡号,isnull(存款金额,0)&nbsp;as&nbsp;金额&nbsp;From&nbsp;a&nbsp;<br>union&nbsp;<br>Select&nbsp;卡号,&nbsp;-1*Isnull(取款金额,0)&nbsp;As&nbsp;金额&nbsp;From&nbsp;b)&nbsp;<br>as&nbsp;Result<br>Group&nbsp;by&nbsp;卡号<br><br>即先把存取款记录汇总到一个表中，然后分类取和即可<br>建议把以下合并两表的语句创建为一个视图,&nbsp;这样方便易理解<br>Select&nbsp;卡号,isnull(存款金额,0)&nbsp;as&nbsp;金额&nbsp;From&nbsp;a&nbsp;<br>union&nbsp;<br>Select&nbsp;卡号,&nbsp;-1*Isnull(取款金额,0)&nbsp;As&nbsp;金额&nbsp;From&nbsp;b

select&nbsp;卡表.*,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;isnull(IncomeTotal,0)&nbsp;IncomeTotal,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;isnull(OutTotal,0)&nbsp;OutTotal,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;isnull(IncomeTotal,0)-isnull(OutTotal,0)&nbsp;余额<br>from&nbsp;卡表<br>left&nbsp;join<br>&nbsp;&nbsp;&nbsp;&nbsp;(select&nbsp;a.卡号,sum(a.存款金额)&nbsp;IncomeTotal<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;a<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;group&nbsp;by&nbsp;a.卡号)&nbsp;a<br>on&nbsp;卡表.卡号=a.卡号<br>left&nbsp;join<br>&nbsp;&nbsp;&nbsp;&nbsp;(select&nbsp;b.卡号,sum(b.取款金额)&nbsp;OutTotal<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;b<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;group&nbsp;by&nbsp;b.卡号)&nbsp;b<br>on&nbsp;卡表.卡号=b.卡号

select&nbsp;c.*,&nbsp;isnull(i_amount)-isnull(o_amount)&nbsp;帐户余额&nbsp;from&nbsp;&nbsp;卡表&nbsp;c<br>&nbsp;&nbsp;left&nbsp;join(<br>&nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;ti.卡号,&nbsp;sum(ti.存金额)&nbsp;i_amount&nbsp;from&nbsp;存明细&nbsp;ti<br>&nbsp;&nbsp;&nbsp;&nbsp;where&nbsp;ti.卡号=c.卡号&nbsp;group&nbsp;by&nbsp;ti.卡号)<br>&nbsp;&nbsp&nbsp;i&nbsp;on&nbsp;c.卡号=i.卡号<br>&nbsp;&nbsp;left&nbsp;join(<br>&nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;to.卡号,&nbsp;sum(to.取金额)&nbsp;o_amount&nbsp;from&nbsp;取明细&nbsp;to<br>&nbsp;&nbsp;&nbsp;&nbsp;where&nbsp;to.卡号=c.卡号&nbsp;group&nbsp;by&nbsp;to.卡号)<br>&nbsp;&nbsp&nbsp;o&nbsp;on&nbsp;c.卡号=o.卡号<br><br>另，建议必须建立卡号，最好建立冗余余额字段

select&nbsp;a.卡号,(sum(a.存入金额)-sum(b.取出金额))&nbsp;as&nbsp;'帐户余额'&nbsp;from&nbsp;a&nbsp;,b&nbsp;where&nbsp;&nbsp;a.卡号=b.卡号&nbsp;group&nbsp;by&nbsp;a.卡号