14-票价问题

create table person(
name string,
station string
)
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile;
load data local inpath 'person.txt' into table person;

create table ticket(
station string,
money string
)
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile;
load data local inpath 'ticket.txt' into table ticket;


条件过滤语句
case when xxx else xxxx end

问题1：按照站数算对应价格
select p.name,p.station,t.money
from person p,ticket t where p.station=t.station


问题2：10站以上打8折

case when condition then operation else operation end

select p.name,p.station,case when p.station>10 then t.money*0.8 else t.money end
from person p,ticket t where p.station=t.station


select p.name,case when t.station>10 then t.money*0.8 else t.money end
from person p,ticket t
where p.station = t.station

问题3：10-15站打9折、15站以上打8折

select p.name,p.station,case when p.station>10 then (case when p.station>15 then t.money*0.8 else t.money*0.9 end)  else t.money end
from person p,ticket t where p.station=t.station

select p.name,case when t.station>10 then (case when t.station>15 then t.money*0.8 else t.money*0.9 end) else t.money end
from person p,ticket t
where p.station = t.station

 

huang    26
lili    25
dongdong    13
wangxiao    5

1    5
2    5
3    5
4    5
5    5
6    5
7    5
8    5
9    5
10    10
11    10
12    10
13    10
14    10
15    10
16    10
17    10
18    30
19    30
20    30
21    30
22    30
23    30
24    30
25    30
26    30
27    30
28    30
29    30
30    30
31    30
32    30
33    30
34    30
35    30
36    30
37    30
38    30
39    30
40    30