MySQL查询来计算图书馆的图书存量

我有三个表，tblinventory，tbldisbursement，tblmissinglost。我想在扣除当天的支出和退货后计算图书总存量。

select bn.No_of_books_procured
     - (count(mis.Unr_ret_donated_discareded))
     - (count(case when dis.dis_disbursed_return = 1
                   then dis.dis_disbursed_return end ) )as Stock
  from tbl_inventorylibbooks bn
 right
  join tbl_limgt_booksmissing_lost_adjust mis
    on mis.Book_Name_missingbk = bn.Id_inventory
  left
  join tbllibmange_disbursement dis
    on dis.dis_book_name_fk_id = bn.Id_inventory
 where bn.Id_inventory = 14

在少数几个条目中，我得到了正确的答案，而在其他一些结果条目中，查询显示了错误的答案。您能为此提供解决方案吗？

tblinventory

id  Name
----------
1   xyz

考虑采购的总书数= 10

支出

**booknameFK  disbursed/returned**
----------------------------------------
Booknafk1                 1
Booknafk2                 0
Booknafk3                 1

其中1 =已支出，0 =已归还

迷失

**id BooknameFk  missng**
----------------------------------------
1    Booknafk1    lost
2    Booknafk1    lost

----------------------------------------

同时检查此查询

select (bn.No_of_books_procured) as procured,
    count(distinct mis.Id_missingbooks_pid) as missing,
    count( case when bs.dis_disbursed_return!=0 then
    bs.dis_disbursed_return end) as disbursed
from tbl_limgt_booksmissing_lost_adjust mis
join tbllibmange_disbursement bs on
mis.book_name_missingbk=bs.dis_book_name_fk_id
join tbl_inventorylibbooks bn on bs.dis_book_name_fk_id=bn.Id_inventory
where bn.Id_inventory=14 group by bn.Id_inventory

我收到重复的条目

最佳答案

选择(
从tblinventory中选择Name，1 BookCount
联盟
选择booknameFK Name，当支出或返回1时的情况，然后-1 else 1从tbldisbursement终止为BookCount
联盟
从tblmissinglost中选择booknameFK Name，-1 BookCount
T

到现在为止，这将为您提供库存的书籍总数。