select 车号表.chehao,SUM(油耗表.yuhao),SUM(运单表.yudan)
from 车号表 inner join 油耗表 on 车号表.Idch=油耗表.idch
inner join 运单表 on 车号表.Idch=运单表.idch
group by 车号表.chehao

------解决方案--------------------

SQL code


select c.chehao,sum(y.yuhao) as'油耗量',sum(yd.yudan)as '运单量'
from [车号] c inner join [油耗] y on y.idch = c.idch
              inner join [运单] yd on yd.idch = c.idch 
where y.ytime>='开始时间'and y.ytime<='结束时间' and yd.ytime >='开始时间'and yd.ytime<='结束时间'
group by c.chehao

------解决方案--------------------

SQL code

if exists (select 1 from sysobjects where id = object_id('CH') and type = 'U')
  drop table CH
--车号表 
create table CH
(Idch int identity(1,1),
chehao nvarchar(50)
)
if exists (select 1 from sysobjects where id = object_id('HY') and type = 'U')
  drop table HY
--油耗表 
create table HY
(id  int identity(1,1),
idch int,
yuhao int,
ytime datetime
)
if exists (select 1 from sysobjects where id = object_id('YH') and type = 'U')
  drop table YH
--油耗表 
create table YH
(id  int identity(1,1),
idch int,
yudan int,
ytime datetime
)

insert into CH
select '苏112' union all
select '沪110' 

insert into HY(idch,yuhao,ytime)
select 1,100,getdate() union all
select 1,200,getdate() union all
select 2,100,getdate() union all
select 2,50,getdate()

insert into YH(idch,yudan,ytime)
select 1,50,getdate() union all
select 1,32,getdate() union all
select 2,30,getdate() union all
select 2,30,getdate()

--select * from CH
--select * from HY
--select * from YH

select distinct chehao as 车号,(select sum(yuhao) from HY where idch=c.Idch) as 耗油量,
(select sum(yudan) from YH where idch=c.Idch) as 运单量
 from CH as c