Question

SQL多个关联表求和语句

3个表结构如下
tb1
客户ID,其它字段
tb2
客户ID ,项目ID, 合同数量
tb3
项目ID, 日期,完成数量

一个"客户ID"对应多个"项目ID",一个"项目ID"分多次完成.

希望通过一个SQL语句得到 某个"客户ID"对应的总的 "合同数量"和"完成数量"
谢谢大家的回复,不过都有点问题.
假设一个"项目ID" 分n次完成,使用该查询后,该"项目ID"对应的"合同数量"会被汇总n次.

Answer 1

设查询客户ID为'A0001',查询语句如下:

select
a.客户ID,
a.项目ID,
sum(a.合同数量) as 该项目总的合同数量,
sum(b.完成数量) as 该项目总的完成数量
from tb2 a,tb3 b
where a.项目ID=b.项目ID and a.客户ID='A0001'
group by a.客户ID,a.项目ID

如果还要包含其他字段:代码如下:

select
c.客户ID,c.项目ID,c.该项目总的合同数量,c.该项目总的完成数量,d.其他字段
from
(select a.客户ID,a.项目ID,sum(a.合同数量) as 该项目总的合同数量, sum(b.完成数量) as 该项目总的完成数量 from tb2 a,tb3 b where a.项目ID=b.项目ID and a.客户ID='A0001' group by a.客户ID,a.项目ID ) c,tb1 d
where
c.客户ID=d.客户ID

追问

谢谢回复,但是还有点问题,请再看下我的补充.

追答

select c.客户ID,c.项目ID,c.该项目总的完成数量,
d.合同数量 from
(select
a.客户ID as 客户ID,
a.项目ID as 项目ID,
sum(b.完成数量) as 该项目总的完成数量
from tb2 a,tb3 b
where a.项目ID=b.项目ID and a.客户ID='A0001'
group by a.客户ID,a.项目ID
) c,tb2 d
where c.客户ID=d.客户ID and c.项目ID=d.项目ID

Answer 2

select a.客户ID,sum(b.合同数量),sum(c.完成数量)
from tb1 a left join tb2 b on a.客户ID=b.客户ID
inner join tb3 c on b.项目ID= c.项目ID
group by a.客户ID

追问

谢谢回复,但是还有点问题,请再看下我的补充.

Answer 3

create table #table1(customerid int)
create table #table2(customerid int, projectid int, totalContract int)
create table #table3(projectid int, projectdate datetime, totalComplete int)

declare @customerid int
set @customerid =1

select a.customerid,
sum(totalContract),
sum(totalcomplete)
from
#table1 a left join #table2 b
inner join #table3 c
on b.projectid=c.projectid
on a.customerid = b.customerid
where a.customerid=@customerid
group by a.customerid

drop table #table1
drop table #table2
drop table #table3

追问

谢谢回复,但是还有点问题,请再看下我的补充.