select a.bookno,a.bookname,sum(a.shu) 总数量
,sum(case when 类别='借出' then Mshu else 0 end) 借出
,sum(case when 类别='还入' then Mshu else 0 end) 还入
,sum(c.bshu) 报损
,sum(a.shu)-sum(c.bshu) 现存
from 基础表 a left join 借阅表 b a.bookno=b.bookno
left join 报损表 c a.bookno=c.bookno
group by a.bookno,a.bookname,b.bookno,c.bookno
,sum(case when 类别='借出' then Mshu else 0 end) 借出
,sum(case when 类别='还入' then Mshu else 0 end) 还入
,sum(c.bshu) 报损
,sum(a.shu)-sum(c.bshu) 现存
from 基础表 a left join 借阅表 b a.bookno=b.bookno
left join 报损表 c a.bookno=c.bookno
group by a.bookno,a.bookname,b.bookno,c.bookno
在借出,还入的时候用trigger来控制这些数量的增减,查询也方便得多个人建议,仅供参考 ^_^
!
a.数量 as 总数量,
IsNull(sum(case when b.类别 = '借出' then b.数量 else 0 end),0) as 借出,
IsNull(sum(case when b.类别 = '还入' then b.数量 else 0 end),0) as 还入,
IsNull(sum(c.数量),0) as 报损
数量-IsNull(sum(case when b.类别 = '借出' then b.数量 else 0 end),0)
+ IsNull(sum(case when b.类别 = '还入' then b.数量 else 0 end),0)
- IsNull(sum(c.数量),0) as 现存
from 基础表 a
left join 借阅表 b on a.图书编号 = b.图书编号
left join 报损表 c on a.图书编号 = b.图书编号
group by a.图书编号,a.图书名称,a.数量
Select a.图书编号,a.图书名称,
sum(a.数量) as 总数量,
IsNull(sum(case when b.类别 = '借出' then b.数量 else 0 end),0) as 借出,
IsNull(sum(case when b.类别 = '还入' then b.数量 else 0 end),0) as 还入,
IsNull(sum(c.数量),0) as 报损
sum(数量)-IsNull(sum(case when b.类别 = '借出' then b.数量 else 0 end),0)
+ IsNull(sum(case when b.类别 = '还入' then b.数量 else 0 end),0)
- IsNull(sum(c.数量),0) as 现存
from 基础表 a
left join 借阅表 b on a.图书编号 = b.图书编号
left join 报损表 c on a.图书编号 = c.图书编号
group by a.图书编号,a.图书名称估计原语句错误的原因是他
的表联接条件(left join 报损表 c on a.图书编号 = b.图书编号)写错,
因此出现连接后出现重复记录,导致统计数据出错
结果是:总数量都多了4倍。借出多了2倍,多了2倍,多了2倍,现存的数字是对的大家都来解决吧
Select a.bookno,a.bookname,a.shu,
(select sum(mshu) from 借阅表 where bookno = a.bookno and 类别 = '借出'),
(select sum(mshu) from 借阅表 where bookno = a.bookno and 类别 = '还入'),
(select sum(bshu) from 报损表 where bookno = a.bookno ),
a.shu - (select sum(mshu) from 借阅表 where bookno = a.bookno and 类别 = '借出')
+ (select sum(mshu) from 借阅表 where bookno = a.bookno and 类别 = '还入')
- (select sum(bshu) from 报损表 where bookno = a.bookno )
from 基础表 a
用你的方法问题已经解决!谢谢!你的帮助!如果我要用Bookname进行模糊查询呢!
Select a.图书编号,a.图书名称,
a.数量 as 总数量,
IsNull(sum(case when b.类别 = '借出' then b.数量 else 0 end),0) as 借出,
IsNull(sum(case when b.类别 = '还入' then b.数量 else 0 end),0) as 还入,
IsNull(c.数量,0) as 报损
a.数量-IsNull(sum(case when b.类别 = '借出' then b.数量 else 0 end),0)
+ IsNull(sum(case when b.类别 = '还入' then b.数量 else 0 end),0)
- IsNull(c.数量,0) as 现存
from 基础表 a
left join 借阅表 b on a.图书编号 = b.图书编号
left join 报损表 c on a.图书编号 = c.图书编号
group by a.图书编号,a.图书名称,a.数量,c.数量要进行模糊查询,只要加一个where子句,where b.图书编号 like ‘%’+条件+‘%’
Select a.bookno,a.bookname,a.shu,
(select sum(mshu) from 借阅表 where bookno = a.bookno and 类别 = '借出'),
(select sum(mshu) from 借阅表 where bookno = a.bookno and 类别 = '还入'),
(select sum(bshu) from 报损表 where bookno = a.bookno ),
a.shu - (select sum(mshu) from 借阅表 where bookno = a.bookno and 类别 = '借出')
+ (select sum(mshu) from 借阅表 where bookno = a.bookno and 类别 = '还入')
- (select sum(bshu) from 报损表 where bookno = a.bookno )
from 基础表 a
where a.bookname like '%天龙八部%'
-- where charidnex('天龙八部',a.bookname) > 0