结合查询

组合查询
现在有三个表，车号表，油耗表，运单表；结构如下：
车号表 （Idch,chehao)
油耗表 （id ,idch, yuhao，ytime)
运单表 （id ,idch, yudan,ytime),后两个表的外键idch,为车号表的主键。
如何根据车号来统计，在一定时间内，车的油耗量（yuhao)和运单量（yudan)

显示形式：
车号， 油耗量，运单量，
苏112，100， 300



[解决办法]

SQL code

select 车号表.chehao,SUM(油耗表.yuhao),SUM(运单表.yudan)from 车号表 inner join 油耗表 on 车号表.Idch=油耗表.idchinner join 运单表 on 车号表.Idch=运单表.idchgroup 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 CHselect '苏112' union allselect '沪110' insert into HY(idch,yuhao,ytime)select 1,100,getdate() union allselect 1,200,getdate() union allselect 2,100,getdate() union allselect 2,50,getdate()insert into YH(idch,yudan,ytime)select 1,50,getdate() union allselect 1,32,getdate() union allselect 2,30,getdate() union allselect 2,30,getdate()--select * from CH--select * from HY--select * from YHselect 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