是一对一的。两个表的结构是完全一样的。a.cpbh=b.cpbh and a.lrsj=b.lrsj也是一样的
加distinct >------------------------------------------ select distinct a.cpbh,a.name,sum(a.isum) as chs,sum(b.isum) as fhs,sum(a.isum*a.jg_zj) as zjxss,sum(b.isum*b.jg_zj) as zjjse, from chd_cpmx as a,fhd_cpmx as b where a.cpbh=b.cpbh and a.lrsj=b.lrsj group by a.cpbh,a.name ,b.cpbh,b.name order by a.cpbh
--我做的简单测试 create table c1 (id1 int,id2 int,v1 int) go create table c2 (id3 int ,id4 int ,v1 int) go insert into c1 select 1,1,1 union all select 1,2,1 union all select 1,3,1 union all select 2,1,2 union all select 2,2,2 union all select 2,3,2 insert into c2 select 1,1,3 union all select 1,2,3 union all select 1,3,3 union all select 2,1,4 union all select 2,2,4 union all select 2,3,4select a.id1,a.id2,sum(a.v1) as v1,sum(b.v1) as v2 from c1 a,c2 b where a.id1=b.id3 and a.id2=b.id4 group by a.id1,a.id2,b.id3,b.id4 order by a.id1 --结果如下: 如果是一一对应关系应该不会有错 id1 id2 v1 v2 ----------------------------- 1 1 1 3 1 2 1 3 1 3 1 3 2 1 2 4 2 2 2 4 2 3 2 4
--继续: insert into c2 select 1,1,3 union all select 1,2,3 union all select 1,3,3 union all select 2,1,4 union all select 2,2,4 union all select 2,3,4select a.id1,a.id2,sum(a.v1) as v1,sum(b.v1) as v2 from c1 a,c2 b where a.id1=b.id3 and a.id2=b.id4 group by a.id1,a.id2,b.id3,b.id4 order by a.id1--结果:v1双倍的数据 id1 id2 v1 v2 ------------------------------ 1 1 2 6 1 2 2 6 1 3 2 6 2 1 4 8 2 2 4 8 2 3 4 8
>------------------------------------------
select distinct a.cpbh,a.name,sum(a.isum) as chs,sum(b.isum) as fhs,sum(a.isum*a.jg_zj) as zjxss,sum(b.isum*b.jg_zj) as zjjse,
from chd_cpmx as a,fhd_cpmx as b
where a.cpbh=b.cpbh and a.lrsj=b.lrsj
group by a.cpbh,a.name ,b.cpbh,b.name
order by a.cpbh
create table c1 (id1 int,id2 int,v1 int)
go
create table c2 (id3 int ,id4 int ,v1 int)
go
insert into c1
select 1,1,1
union all select 1,2,1
union all select 1,3,1
union all select 2,1,2
union all select 2,2,2
union all select 2,3,2
insert into c2
select 1,1,3
union all select 1,2,3
union all select 1,3,3
union all select 2,1,4
union all select 2,2,4
union all select 2,3,4select a.id1,a.id2,sum(a.v1) as v1,sum(b.v1) as v2 from c1 a,c2 b
where a.id1=b.id3 and a.id2=b.id4
group by a.id1,a.id2,b.id3,b.id4
order by a.id1
--结果如下: 如果是一一对应关系应该不会有错
id1 id2 v1 v2
-----------------------------
1 1 1 3
1 2 1 3
1 3 1 3
2 1 2 4
2 2 2 4
2 3 2 4
insert into c2
select 1,1,3
union all select 1,2,3
union all select 1,3,3
union all select 2,1,4
union all select 2,2,4
union all select 2,3,4select a.id1,a.id2,sum(a.v1) as v1,sum(b.v1) as v2 from c1 a,c2 b
where a.id1=b.id3 and a.id2=b.id4
group by a.id1,a.id2,b.id3,b.id4
order by a.id1--结果:v1双倍的数据
id1 id2 v1 v2
------------------------------
1 1 2 6
1 2 2 6
1 3 2 6
2 1 4 8
2 2 4 8
2 3 4 8