从不同表中获取两列差异的有力查询

Question

我必须使用雄辩的查询显示剩余的书籍数量。我可以从这张桌子上得到买书的总数。

ordered_books
BOOKCODE[varchar(10)]   QUNATITY[varchar(6)]
111                         25
423                         15
201                         10
111                         10
423                         10
201                         5
158                         12

首先，我必须对每一本书代码的总图书进行汇总。然后，从另一张表中，我要计算出差额。

books_out
DISTRIBUTOR[varchar(50)]    BOOKCODE[varchar(10)]           QUNATITY[varchar(6)]    
25                              158                             2
35                              201                             5
45                              158                             5
55                              111                             10
35                              111                             5
15                              423                             1
25                              423                             10

再一次，从这个表中，我必须计算出分销商拿走的图书总数，然后才能得到实际的图书数量。我应该如何为这个场景写出雄辩的疑问呢？

Answer 1

关于您的场景，我认为您需要这个查询，请尝试并评论它是否有用。

$countBooks = DB::select(DB::Raw(" SELECT A.TOTAL, IFNULL(B.SOLD,0) AS SOLD, (A.TOTAL - IFNULL(B.SOLD,0)) AS REMAINING FROM 
                                                (
                                                    select t1.BookID, SUM(t1.QUNATITY) AS TOTAL from ordered_books t1 
                                                    GROUP BY t1.BookID) A 
                                                LEFT JOIN 
                                                (
                                                    SELECT BookCode, IFNULL(SUM(Quantity),0) AS SOLD FROM books_outs GROUP BY BookCode) B 
                                                ON A.BookID = B.BookCode
                                    "));

dump($countBooks);

另外，最好是显示图书的名称，而不是代码。它将是更加友好的用户。谢谢！

Answer 2

​

Try to use Raw query like this
==================================

$data=DB::select(DB::Raw("select t1.BOOKCODE,(total-selled) as available from (select BOOKCODE, SUM(QUNATITY) as total FROM `ordered_books` group by `BOOKCODE`) as t1,(SELECT BOOKCODE,SUM(QUNATITY) as selled FROM `books_out` group by `BOOKCODE`) as t2 where t1.BOOKCODE=t2.`BOOKCODE`"));
        
print_r($data);

//To get total books for each of the book code
SELECT BOOKCODE,SUM(QUNATITY) FROM `ordered_books` group by `BOOKCODE`

//To get total books_out for each of the book code
SELECT BOOKCODE,SUM(QUNATITY) FROM `books_out` group by `BOOKCODE`

​