请教一条简单的SQL语句
小弟还是学生 数据库学的不好 写课程设计的时候遇到一个问题

A表 有studentid bookid amount字段 分别代表学号 书号 和数量 指的是某学生需要某书的数量
B表 有bookid stock 指的是书的库存量
现在想知道每本书的缺书量 就是将A表中相同bookid的书的amount加起来然后如果大于B表中该书的stock 就相减然后得出结果
 
请教这样一条SQL 应该不难的但就是写不对 我用的是MYSQL 5.5 先谢谢了

------解决方案--------------------
select A.bookid , (A.amount-B.stock) as chazhi
from A,B 
where A.bookid = B.bookid and (A.amount-B.stock)>0;

------解决方案--------------------
select u.bookid, u.total-v.stock
from (
select bookid,sum(amount) as total
from A表
group by bookid
) u , B表 v
where u.bookid=v.bookid
and u.total>v.stock
------解决方案--------------------
select a1.bookid,if(a1.total>b1.stock,a1.total-b1.stock,a1.total)
from (
select bookid,sum(amount) as total from A表 group by bookid ) a1 inner join B表 b1 on a1.bookid=b1.bookid 

------解决方案--------------------

SQL code

select a.bookid, case when a.total>b.stock  then a.total-b.stock else 0 end as less_number
from 
(
select bookid, sum(ifnull(amount,0)) as total 
from A表 
group by bookid 
)a  inner join B表 b 
on a.bookid=b.bookid