推荐订阅源

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

Your Name: 

Given the following tables:

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

CAR
CarID	Color	Make	Model
1	Blue	Ford	Taurus
2	Green	VW	Jetta
3	Blue	Acura	1.7EL

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:

OdometerReading	Consumption	Color	Make
1245	n/a	Blue	Ford
1457	7.54	Blue	Ford
1872	8.19	Blue	Ford
2193	7.78	Blue	Ford
2448	7.84	Blue	Ford
2508	8.33	Blue	Ford
2663	9.03	Blue	Ford
3075	9.95	Blue	Ford
3387	8.65	Blue	Ford
112145	n/a	Green	VW
112972	4.11	Green	VW
113357	7.79	Green	VW
113731	7.75	Green	VW
114130	9.02	Green	VW
114535	8.88	Green	VW
114943	10.04	Green	VW
18091	n/a	Blue	Acura
18291	8	Blue	Acura
18506	8.37	Blue	Acura
18791	8.07	Blue	Acura
19065	8.02	Blue	Acura
19364	9.36	Blue	Acura
19569	8.78	Blue	Acura

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

 得到的结果如下：

OdometerReading	Consumption	Color	Make
1245	NULL	Blue	Ford
1457	8.00	Blue	Ford
1872	8.25	Blue	Ford
2193	7.79	Blue	Ford
2448	8.00	Blue	Ford
2508	9.20	Blue	Ford
2663	9.13	Blue	Ford
3075	10.15	Blue	Ford
3387	8.90	Blue	Ford
112145	NULL	Green	VW
112972	4.14	Green	VW
113357	7.80	Green	VW
113731	8.00	Green	VW
114130	9.20	Green	VW
114535	9.13	Green	VW
114943	10.15	Green	VW
18091	NULL	Blue	Acura
18291	8.30	Blue	Acura
18506	8.50	Blue	Acura
18791	8.20	Blue	Acura
19065	8.30	Blue	Acura
19364	9.50	Blue	Acura
19569	9.10	Blue	Acura