--构建测试数据 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 行受影响) */
十分感谢,也十分敬佩。根据您的提示,我找到了解决方法,我的是意思可能也没有完全表达清楚,我没有要把两张表的数量重合,表1的数量应该是表2的总量,我的语句如下,再帮我看看能不能再精简一点select sum(shuliang) 合计, '配置' 类别 from peizhi where (gzTime between '2006/5/21' and '2007/5/21') union all select count(*) 合计, '实用' from peizhi a,zhuji b where a.id =b.pzId and isUse = 1 and (gzTime between '2006/5/21' and '2007/5/21') union all select count(*) 合计, tyLy from peizhi a,zhuji b where a.id =b.pzId and isUse = 0 and (gzTime between '2006/5/21' and '2007/5/21') group by tyLy 结果 50 配置 4 实用 2 丢失 2 其他
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 行受影响)
*/
union all
select count(*) 合计, '实用' from peizhi a,zhuji b where a.id =b.pzId and isUse = 1 and (gzTime between '2006/5/21' and '2007/5/21')
union all
select count(*) 合计, tyLy from peizhi a,zhuji b
where a.id =b.pzId and isUse = 0 and (gzTime between '2006/5/21' and '2007/5/21') group by tyLy
结果
50 配置
4 实用
2 丢失
2 其他