注：student 表的sid 分别都是studentjf和studenttf表的snameid的主键。
表：student(学生基础表)sid name
1 李磊
2 莉莉表：studentjf（学生交费表）jfid snameid xuefei shufei heji
23 1 10 10 20
24 1 10 10 20
25 1 10 10 20表：studenttf（学生退费表）tfid snameid xuefei shufei heji
12 1 10 10 20
我想要的效果是这样的。sid name jxuefei jshufei jheji txuefei tshufei theji 总合计(zhj)
1 李磊 30 30 60 10 10 20 40
sum(j.heji) as jheji,sum(t.xuefei) as txuefei,sum(t.shufei) as tshufei,sum(t.heji) as theji,(sum(j.heji)-sum(t.heji)) as zhj
from student s,studentjf j,studenttf t
where s.sid = j.snameid and s.id = t.snameid
group by s.sid,s.name
inner join
studentjf b
on a.sid=b.sid
inner join
sudenttf c
on a.sid=c.sid
go
create table student
(
sid int,
name varchar(20)
)
insert into student select 1,'李磊'
insert into student select 2,'莉莉'
go
if object_id('studentjf') is not null drop table studentjf
go
create table studentjf
(
jfid int,
snameid int,
xuefei int,
shufei int,
heji int
)
insert into studentjf select 23,1,10,10,20
insert into studentjf select 24,1,10,10,20
insert into studentjf select 25,1,10,10,20
go
if object_id('studenttf') is not null drop table studenttf
go
create table studenttf
(
tfid int,
snameid int,
xuefei int,
shufei int,
heji int
)
insert into studenttf select 12,1,10,10,20
goselect s.sid,s.name,
js.jxuefei,js.jshufei,js.jheji,
tx.txuefei,tx.tshufei,tx.theji,
(js.jheji-tx.theji) '总合计'
from student s
join
(
select snameid,sum(xuefei) jxuefei,sum(shufei) jshufei,sum(heji) jheji from studentjf group by snameid
) js
on s.sid=js.snameid
left join
(
select snameid,sum(xuefei) txuefei,sum(shufei) tshufei,sum(heji) theji from studenttf group by snameid
) tx
on s.sid=tx.snameidsid name jxuefei jshufei jheji txuefei tshufei theji 总合计
----------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 李磊 30 30 60 10 10 20 40(1 行受影响)
declare @student table (sid int,name varchar(10))
insert @student select 1 ,'李磊'
insert @student select 2 ,'莉莉'
declare @studentjf table (jfid int, snameid int,xuefei int,shufei int,heji int)
insert @studentjf select 23 ,1 ,10 ,10 ,20
insert @studentjf select 24 ,1 ,10 ,10 ,20
insert @studentjf select 25 ,1 ,10 ,10 ,20
declare @studenttf table (tfid int,snameid int,xuefei int,shufei int,heji int)insert @studenttf select 12,1,10,10,20
select
a.*,b.shufei,b.xuefei,b.heji,c.shufei,c.xuefei,c.heji,b.heji-c.heji as zhj
from @student a
join (select snameid,SUM(xuefei) as xuefei,SUM(shufei) as shufei,SUM(heji) as heji from @studentjf group by snameid) b on a.sid=b.snameid
join (select snameid,SUM(xuefei) as xuefei,SUM(shufei) as shufei,SUM(heji) as heji from @studenttf group by snameid) c on a.sid=c.snameid
sid name shufei xuefei heji shufei xuefei heji zhj
----------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 李磊 30 30 60 10 10 20 40(1 行受影响)
if object_id('tempdb.dbo.#student') is not null drop table #student
create table #student(sid int, name varchar(8))
insert into #student
select 1, '李磊' union all
select 2, '莉莉'
--> 测试数据：#studentjf
if object_id('tempdb.dbo.#studentjf') is not null drop table #studentjf
create table #studentjf(jfid int, snameid int, xuefei int, shufei int, heji int)
insert into #studentjf
select 23, 1, 10, 10, 20 union all
select 24, 1, 10, 10, 20 union all
select 25, 1, 10, 10, 20
--> 测试数据：#studenttf
if object_id('tempdb.dbo.#studenttf') is not null drop table #studenttf
create table #studenttf(tfid int, snameid int, xuefei int, shufei int, heji int)
insert into #studenttf
select 12, 1, 10, 10, 20select a.sid, a.name,
isnull(b.x,0) jxuefei,
isnull(b.s,0) jshufei,
isnull(b.h,0) jheji,
isnull(c.x,0) txuefei,
isnull(c.s,0) tshufei,
isnull(c.h,0) theji,
zhj = isnull(b.h-c.h,0)
from #student a
left join
(select snameid, sum(xuefei)x, sum(shufei)s, sum(heji)h from #studentjf group by snameid) b
on a.sid = b.snameid
left join
(select snameid, sum(xuefei)x, sum(shufei)s, sum(heji)h from #studenttf group by snameid) c
on a.sid = c.snameid/*
sid name jxuefei jshufei jheji txuefei tshufei theji zhj
----------- -------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 李磊 30 30 60 10 10 20 40
2 莉莉 0 0 0 0 0 0 0
*/
go
--以下为测试数据
create table student
(
sid int,
name varchar(20)
)
insert into student select 1,'李磊'
insert into student select 2,'莉莉'
go
if object_id('studentjf') is not null drop table studentjf
go
create table studentjf
(
jfid int,
snameid int,
xuefei int,
shufei int,
heji int
)
insert into studentjf select 23,1,10,10,20
insert into studentjf select 24,1,10,10,20
insert into studentjf select 25,1,10,10,20
go
if object_id('studenttf') is not null drop table studenttf
go
create table studenttf
(
tfid int,
snameid int,
xuefei int,
shufei int,
heji int
)
insert into studenttf select 12,1,10,10,20
go--以下为查询语句
select sid,(select SUM(xuefei) from studentjf where snameid=sid) as jxuefei,
(select SUM(shufei) from studentjf where snameid=sid) as jshufei,
(select SUM(heji) from studentjf where snameid=sid) as jheji,
(select SUM(xuefei) from studenttf where snameid=sid) as txuefei,
(select SUM(shufei) from studenttf where snameid=sid) as tshufei,
(select SUM(heji) from studenttf where snameid=sid) as theji,
(select SUM(heji) from studentjf where snameid=sid)-(select SUM(heji) from studenttf where snameid=sid) as zhj
from student