无意间在网上看到一个外企的SQL面试题，请大家来讨论何解？
    题目如下：

Your Name: 

Given the following tables:

Take it as a given that every time a car is refueled, the tank is filled.

To determine gas consumption (l/100km), take the LitersGas, divide it by kilometers traveled, and multiply by 100.

For example:

If the car takes 45 liters of gas, and had traveled 502 km since the previous time it was refueled, the fuel consumption would be: 45 liters / 502 km * 100 = 8.96 l/100km

1. Is it possible to make a single sql statement that would determine the fuel consumption and return the following table:

2. If you answered yes to question 1, please give the sql statement.

If you answered no, please explain why it isn’t possible, what extra information is needed and give a sample sql statement that should give the answer above.

我在SQL SERVER 2000下测试了一下，不知道是没有理解题目意思还是说题目给出的答案有问题，反正就是没对上。请大家来评评：

use tempdb
create table #Refuel
(
 CarID int,
 OdometerReading int,
 LitersGas numeric(4,2)
)
go

create table #car
(
 CarID int,
 Color varchar(200),
 Make varchar(200),
 Model varchar(200)
)
go

insert into #car values(1,'Blue','Ford','Taurus')
insert into #car values(2,'Green','VW','Jetta')
insert into #car values(3,'Blue','Acura','1.7EL')

insert into #Refuel values(1,1245,55.25)
insert into #Refuel values(1,1457,16.96)
insert into #Refuel values(1,1872,34.23)
insert into #Refuel values(1,2193,25)
insert into #Refuel values(1,2448,20.4)
insert into #Refuel values(1,2508,5.52)
insert into #Refuel values(1,2663,14.15)
insert into #Refuel values(1,3075,41.82)
insert into #Refuel values(1,3387,27.77)
insert into #Refuel values(2,112145,36)
insert into #Refuel values(2,112972,34.24)
insert into #Refuel values(2,113357,30.03)
insert into #Refuel values(2,113731,29.92)
insert into #Refuel values(2,114130,36.7)
insert into #Refuel values(2,114535,36.98)
insert into #Refuel values(2,114943,41.41)
insert into #Refuel values(3,18091,30.25)
insert into #Refuel values(3,18291,16.6)
insert into #Refuel values(3,18506,18.27)
insert into #Refuel values(3,18791,23.37)
insert into #Refuel values(3,19065,22.74)
insert into #Refuel values(3,19364,28.4)
insert into #Refuel values(3,19569,18.65)

select a.OdometerReading,Convert(numeric(4,2),a.LitersGas/(a.OdometerReading-(select top 1 c.OdometerReading from #Refuel c where c.carid=a.carid and c.OdometerReading<a.OdometerReading order by c.OdometerReading desc))*100) as 'Consumption', b.Color,b.Make from #Refuel a inner join #car b on a.carid=b.carid

drop table #Refuel

drop table #car

 得到的结果如下：