无法计算库存当前库存

Question

我有两个表，其结构如下：

tblBookMst

Id       bk_isbn        bk_title      bk_author
----------------------------------------------------
1        ISBN_001       ABC Book      AA  
2        ISBN_002       DEF Book      BB
3        ISBN_003       GHI Book      CC
4        ISBN_004       JKL Book      DD

和

tblBookId
b_id         id             lib_id        inv_stat
----------------------------------------------------
1            1             BK/LIB/01      1
2            1             BK/LIB/02      2
3            1             BK/LIB/03      2
4            2             BK/LIB/04      1
5            2             BK/LIB/05      1
6            3             BK/LIB/06      1   

(“inv_stat”图例:股票中的1=> &流通中的2 => )使用上面的2个表，我想编写一个查询，它将为我提供如下所示的输出

bk_title       bk_author     tot_copies     in_stock     in_circulation
ABC Book       AA            3              1            2
DEF Book       BB            2              2            0
GHI Book       CC            1              1            0

到目前为止，我还不知道如何计算'in_stock‘&’in_ below‘，我正在使用下面提到的sql查询。

SELECT a.id,a.bk_title,a.bk_author,count(b.lib_id) as tot_copies
FROM tblBookMst a
JOIN tblBookId b ON a.id = b.id
GROUP BY a.id,a.bk_title,a.bk_author
ORDER BY a.bk_title

我希望你能用例子理解我的question.Please建议

Answer 1

你就快到了！你只需要一些案例陈述

SELECT a.id,
    a.bk_title,
    a.bk_author,
    count(b.lib_id) as tot_copies
    SUM(CASE WHEN b.inv_stat = 1 THEN 1 ELSE 0 END) as in_stock,
    SUM(CASE WHEN b.inv_stat = 2 THEN 1 ELSE 0 END) as in_circulation
FROM tblBookMst a
JOIN tblBookId b ON a.id = b.id
GROUP BY a.id,a.bk_title,a.bk_author
ORDER BY a.bk_title