in欠帐
-----------------
id name inprice
1 tom 50
2 tom 862.53
3 tom 511.12
out付帐
------------------
id name outprice
1 tom 60
2 tom 30
3 tom 600.35
用一条语句求出：当前某人应当前欠帐。
（50+862.53+511.12）-（60+30+600.35）=？
所有数据为float数据。小数运算时全部只取小数点后面2位。
求这个语句啊/。
------解决方案--------------------
in欠帐
-----------------
id name inprice
1 tom 50
2 tom 862.53
3 tom 511.12
out付帐
------------------
id name outprice
1 tom 60
2 tom 30
3 tom 600.35
-----------------
select a.name, isnull(a.inprice,0 )-isnull(b.outprice ,0)as 欠料
from
(select name, sum(inprice) as inprice from t1 group by name
)a
full join
(select name,sum(outprice) as outprice from t2 group by name
)b
on a.name=b.name
on
------解决方案--------------------
select id,name,sum(price)
from
(select id, name , convert(numeric(10,2), inprice ) as price
from in
union
select id , name , convert(numeric(10,2), -outprice ) as price
from out) a
group by id,name
------解决方案--------------------
convert(numeric(10,4),(select sum(intprice) from in where id= " ")- (select sum(outprice) from out where id= " "))
------解决方案--------------------
create proc [dbo].[qianxhang]
(@name)
as
select inprice=(sum(inprice)-sum(outprice)) from in欠帐 left join out付帐 on in欠帐.name =out付帐.name where [email protected]