用于计算图书馆图书库存的Mysql查询
用于计算图书馆图书库存的Mysql查询
提问于 2016-03-16 12:39:42
我有三张桌子--库存表、付款表、遗失表。我想在扣除当日的支出和回报后，计算出总库存。
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在少数条目上，我得到了正确的答案，而其他结果条目则显示了错误的答案。您能提供一个解决方案吗？
塔布存货
id Name
----------
1 xyz考虑总图书procured=10
转帐
**booknameFK disbursed/returned**
----------------------------------------
Booknafk1 1
Booknafk2 0
Booknafk3 11=disbursed和0=returned
失联
**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我得到了重复的条目
回答 1
Stack Overflow用户
发布于 2016-07-21 18:43:11
选择Sum( BookCount ) Stock (选择名称，从tblinventory联合选择1 BookCount，选择booknameFK名称，当disbursedORreturned=1然后-1其他1结束时，BookCount从tblinventory选择booknameFK名称，-1 BookCount从tblmissinglost )T
到目前为止，这将给你库存的图书总数。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/36035955
复制相关文章
相似问题
腾讯云开发者
