select bzid,bzname,sum(cpje)cpje,sum(clje)clje ,(sum(cpje)cpje-sum(clje)clje)* 0.97 as je from a group by bzid,bzname -----mssql
select a.bzid,a.bzname,sum(cpje)cpje,sum(clje)clje ,(sum(cpje)cpje-sum(clje)clje)* 0.97 as je from a left join b on a.bzid=b.bzid group by a.bzid,a.bzname
create table BzList(ID int,BzName nvarchar(10)) insert into bzlist select 1,'A组' insert into bzlist select 2,'B组' create table Clhis(BzID int,BzName nvarchar(10),ClJe int) insert into Clhis select 1,'A组',200 insert into Clhis select 2,'B组',100 insert into Clhis select 2,'B组',350 insert into Clhis select 1,'A组',300 create table Cphis(BzID int,BzName nvarchar(10),CpJe int) insert into Cphis select 2,'B组',400 insert into Cphis select 2,'B组',120 insert into Cphis select 1,'A组',810 insert into Cphis select 2,'B组',220 go select id BzID,BzName,Clje,CpJe,(CpJe-ClJe)*0.97 as Je from( select a.id,a.BzName,a.ClJe,sum(c.CpJe) CpJe from( select a.id,a.BzName,sum(b.ClJe)ClJe from bzlist a inner join clhis b on a.id=b.bzid -- group by a.id,a.BzName )a inner join cphis c on c.bzid=a.id group by a.id,a.BzName,a.ClJe )t /* BzID BzName Clje CpJe Je ----------- ---------- ----------- ----------- --------------------------------------- 1 A组 500 810 300.70 2 B组 450 740 281.30(2 行受影响)*/ go drop table bzlist,clhis,cphis
from a group by bzid,bzname
-----mssql
from a left join b on a.bzid=b.bzid group by a.bzid,a.bzname
insert into bzlist select 1,'A组'
insert into bzlist select 2,'B组'
create table Clhis(BzID int,BzName nvarchar(10),ClJe int)
insert into Clhis select 1,'A组',200
insert into Clhis select 2,'B组',100
insert into Clhis select 2,'B组',350
insert into Clhis select 1,'A组',300
create table Cphis(BzID int,BzName nvarchar(10),CpJe int)
insert into Cphis select 2,'B组',400
insert into Cphis select 2,'B组',120
insert into Cphis select 1,'A组',810
insert into Cphis select 2,'B组',220
go
select id BzID,BzName,Clje,CpJe,(CpJe-ClJe)*0.97 as Je from(
select a.id,a.BzName,a.ClJe,sum(c.CpJe) CpJe from(
select a.id,a.BzName,sum(b.ClJe)ClJe
from bzlist a inner join clhis b on a.id=b.bzid --
group by a.id,a.BzName
)a inner join cphis c on c.bzid=a.id
group by a.id,a.BzName,a.ClJe
)t
/*
BzID BzName Clje CpJe Je
----------- ---------- ----------- ----------- ---------------------------------------
1 A组 500 810 300.70
2 B组 450 740 281.30(2 行受影响)*/
go
drop table bzlist,clhis,cphis
ACCESS小版人气不旺,所以就来这里了,见谅,帮帮忙