一个关于出库数量的联合查询问题

[cl9876]

数据库的表如图所示

现在要找出每个班的领书情况（出库-出库退货），要求显示：年级、班级、书名、库存、领出书的数量（出库-出库退货）。
我的语句是

select c.grade,c.classname,b.bname,b.num,a.outnum as outnum from classtable c,book b,
            (select o.classid,o.isbn,(sum(num)-2*(select sum(num) from outstock o1 where o1.classid=o.classid and state='出库退货'))
             as outnum from outstock o group by classid,isbn) a where a.classid=c.classid and a.isbn=b.isbn
go

结果不对啊！！

[cl9876]

楼上的啊，结果不对啊

[cl9876]

谢谢

[等不到来世]

[Quote=引用楼主 cl9876 的帖子:]
select c.grade,c.classname,b.bname,b.num,a.outnum as outnum from classtable c,book b,
(select o.classid,o.isbn,(sum(num)-2*(select sum(num) from outstock o1 where o1.classid=o.classid and o1.isbn=o.isbn and state='出库退货'))
as outnum from outstock o group by classid,isbn) a where a.classid=c.classid and a.isbn=b.isbn
[/Quote]

[等不到来世]

推荐写法：

select c.grade,c.classname,b.bname,b.num,sum(a.num)-2*sum(case a.state when '出库退货' then a.num else 0 end) as outnum
from outstock a 
join book b on a.isbn=b.isbn
join classtable c on a.classid=c.classid
group by c.grade,c.classname,b.bname,b.num

不明白为何要将出库退货的数量×2 ？

[等不到来世]

哦，明白了。。
可用简写为：

select c.grade,c.classname,b.bname,b.num,sum(case a.state when '出库退货' then -a.num else a.num end) as outnum
from outstock a 
join book b on a.isbn=b.isbn
join classtable c on a.classid=c.classid
group by c.grade,c.classname,b.bname,b.num