--构建测试数据
create table peizhi(id int, pihao char(4), gzTime datetime, shuliang int)
insert into peizhi
select 1, '0602', '2006/2/1' ,5 union all 
select 2, '0603', '2006/12/2', 4 union all 
select 3, '0701', '2007/1/3' ,3 union all
select 4, '0702', '2007/5/4' ,2 union all
select 5, '0801', '2008/6/1' ,5 
create table zhuji(id int, pId int, isUse bit, liyou varchar(10)) 
insert into zhuji
select 1, 1, 0, '报废' union all select 
2, 1, 0, '报废' union all select 
3, 1, 0, '报废' union all select 
4, 1, 0, '报废' union all select 
5, 2, 0, '报废' union all select 
6, 2, 0, '报废' union all select 
7, 2, 0, '报废' union all select 
8, 2, 0, '报废' union all select 
9, 3, 1, '' union all select 
10, 3, 0, '丢失' union all select 
11, 3, 1, '' union all select 
12, 4, 1, '' union all select 
13, 4, 0, '其他' union all select 
14, 5, 1, '' union all select 
15, 5, 1, '' union all select 
16, 5, 0, '丢失' union all select 
17, 5, 1, '' union all select 
18, 5, 1,'' 

--解决方案
select sum(shuliang) 合计, '未使用' 类别 from  peizhi a,zhuji b where a.id = b.id and isUse = 0
union all
select isnull(sum(shuliang),0) 合计, '使用中' from  peizhi a,zhuji b where a.id = b.id and isUse = 1 
union all
select sum(shuliang) 合计, liyou from  peizhi a,zhuji b 
where a.id = b.id and isUse = 0 and (gzTime between '2006/12/2' and '2008/6/1') group by liyou
/*
合计          类别
----------- ----------
19          未使用
0           使用中
14          报废

(3 行受影响)


*/