请教一条简单的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
[解决办法]
select a.bookid, case when a.total>b.stock then a.total-b.stock else 0 end as less_numberfrom (select bookid, sum(ifnull(amount,0)) as total from A表 group by bookid )a inner join B表 b on a.bookid=b.bookid
