select 发出单位名=t2.单位名, 接受单位名=t3.单位名, t1.数量 from ( select 发出单位ID, 接受单位ID, 数量=sum(数量) from 表1 group by 发出单位ID, 接受单位ID) t1 join 表2 t2 on t1.发出单位ID=t2.单位id join 表2 t3 on t1.发出单位ID=t3.单位id
create table T1 (fid int,jid int,num int) insert into T1 values(1,2,100) insert into T1 values(1,3,200) insert into T1 values(1,4,300) insert into T1 values(1,2,400) insert into T1 values(1,3,500) create table T2 (id int,name varchar(10)) insert into T2 values(1,'a') insert into T2 values(2,'b') insert into T2 values(3,'c') insert into T2 values(4,'d') goselect name 发出单位名,接受单位名,总数量 from ( select fid,name 接受单位名,sum(num) 总数量 from T1,T2 where T1.jid = T2.id group by fid,name ) t,t2 where t.fid = t2.id
drop table T1,T2/* 发出单位名 接受单位名 总数量 ---------- ---------- ----------- a b 500 a c 700 a d 300(所影响的行数为 3 行) */
create table vlsm1 ( fid int , jid int , num int )delete vlsm1 insert into vlsm1 select 1, 2 ,10 union all select 2 ,1, 10 union all select 3, 3,10 union all select 3, 2,10 union all select 2, 1, 10 union all select 4, 1 ,10 create table vlsm2 ( id int , name char(50) )insert into vlsm2 select 1, 'AAA' union all select 2, 'BBB' union all select 3, 'CCC' union all select 4, 'DDD' union all select 5, 'EEE'select * from vlsm1 select * from vlsm2 select B.name as 发出单位名, C.name as 接受单位名 ,A.num as [sum(数量)] from ( select fid,jid ,sum(num) as num from vlsm1 group by fid,jid ) A inner join vlsm2 B on A.fid = B.id inner join vlsm2 C on A.jid = C.id =========================== BBB AAA 20 DDD AAA 10 AAA BBB 10 CCC BBB 10 CCC CCC 10
from ( select 发出单位ID, 接受单位ID, 数量=sum(数量) from 表1 group by 发出单位ID, 接受单位ID) t1
join 表2 t2 on t1.发出单位ID=t2.单位id
join 表2 t3 on t1.发出单位ID=t3.单位id
insert into T1 values(1,2,100)
insert into T1 values(1,3,200)
insert into T1 values(1,4,300)
insert into T1 values(1,2,400)
insert into T1 values(1,3,500)
create table T2 (id int,name varchar(10))
insert into T2 values(1,'a')
insert into T2 values(2,'b')
insert into T2 values(3,'c')
insert into T2 values(4,'d')
goselect name 发出单位名,接受单位名,总数量 from
(
select fid,name 接受单位名,sum(num) 总数量 from T1,T2 where T1.jid = T2.id group by fid,name
) t,t2
where t.fid = t2.id
drop table T1,T2/*
发出单位名 接受单位名 总数量
---------- ---------- -----------
a b 500
a c 700
a d 300(所影响的行数为 3 行)
*/
create table vlsm1
(
fid int ,
jid int ,
num int
)delete vlsm1
insert into vlsm1
select 1, 2 ,10 union all
select 2 ,1, 10 union all
select 3, 3,10 union all
select 3, 2,10 union all
select 2, 1, 10 union all
select 4, 1 ,10
create table vlsm2
(
id int ,
name char(50)
)insert into vlsm2
select 1, 'AAA' union all
select 2, 'BBB' union all
select 3, 'CCC' union all
select 4, 'DDD' union all
select 5, 'EEE'select * from vlsm1
select * from vlsm2
select B.name as 发出单位名, C.name as 接受单位名 ,A.num as [sum(数量)] from ( select fid,jid ,sum(num) as num from vlsm1 group by fid,jid ) A
inner join vlsm2 B
on A.fid = B.id
inner join vlsm2 C
on A.jid = C.id
===========================
BBB AAA 20
DDD AAA 10
AAA BBB 10
CCC BBB 10
CCC CCC 10