一个外企SQL面试题的困惑

    无意间在网上看到一个外企的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


 



posted @ 2008-03-25 21:16  bobomouse  阅读(1768)  评论(2编辑  收藏  举报