SQL 的问题

see199

麻烦高手帮帮忙，
问题：
car_type(carTypeID, carTypeName)
car(carID, carTypeID)
repair(repairID, carID, cost, date)
我尝试写的SQLcoding，但是得不到要的答案
select
c.carTypeID 'car type',
count(*) 'Total car',
select sum(cost) from repair,
sum(r.totalCost)/count(*)
from car C left join repair r on c.carID=r.carID
group by CT.carTypeID

正常的output应该是这样的：

car type                 total        cost         average
nissan                20        200        10
honda                5        0        0
kia                7        70        10

麻烦各位高手了！非常感谢

see199

对不起，写漏了这个：

total : 汽车的总数量
cost : 本月的总修理费（根据车子的种类）
average : 每辆汽车平均每个月的修理费

悠哉亭

楼主没说明用什么database. 我就当做是 MSSQL.

select
c.carTypeID 'car type',
count(*) 'Total car',
select sum(cost) from repair, ---<<Error
sum(r.totalCost)/count(*)
from car C left join repair r on c.carID=r.carID
group by CT.carTypeID

参考以下example:

create table car_type(carTypeID int, carTypeName varchar(10));
create table car(carID int , carTypeID int );
create table repair(repairID int, carID int, cost int, date datetime);

go

insert into car_type (carTypeID  , carTypeName )
values (1,'nissan');

insert into car_type (carTypeID  , carTypeName )
values (2,'honda');

insert into car_type (carTypeID  , carTypeName )
values (3,'kia');

insert into car_type (carTypeID  , carTypeName )
values (4,'proton');

insert car(carID , carTypeID ) values (1,1);
insert car(carID , carTypeID ) values (2,1);
insert car(carID , carTypeID ) values (3,1);
insert car(carID , carTypeID ) values (4,2);
insert car(carID , carTypeID ) values (5,2);
insert car(carID , carTypeID ) values (6,2);
insert car(carID , carTypeID ) values (7,2);
insert car(carID , carTypeID ) values (8,3);
insert car(carID , carTypeID ) values (9,3);


insert repair(repairID, carID, cost, date)
values (1,1,10,getdate());
insert repair(repairID, carID, cost, date)
values (2,2,20,getdate());
insert repair(repairID, carID, cost, date)
values (3,3,30,getdate());
insert repair(repairID, carID, cost, date)
values (4,4,10,getdate());
insert repair(repairID, carID, cost, date)
values (5,5,20,getdate());
insert repair(repairID, carID, cost, date)
values (6,6,100,getdate());
insert repair(repairID, carID, cost, date)
values (7,7,110,getdate());
insert repair(repairID, carID, cost, date)
values (8,8,20,getdate());
insert repair(repairID, carID, cost, date)
values (9,9,40,getdate());

go

select
ct.carTypeName 'car type',
count(c.carID) 'Total car',
sum(isnull(cost,0)) as cost,
avg(isnull(r.Cost,0)) as average
from car_type ct left join car c
on ct.carTypeId=c.carTypeID
left join repair r on c.carID=r.carID
group by ct.carTypeName;


result:
car type   Total car   cost        average     
---------- ----------- ----------- -----------
honda      4           240         60
kia        2           60          30
nissan     3           60          20
proton     0           0           0


参考 MS SQL 的 Books Online help file 吧

see199

哦！很感谢！可以了