2r7GNgka

1
2-- MySQL
3
4create database if not exists avia;
5use avia;
6drop table if exists test;
7drop function if exists is_hot;
8
9delimiter //
10create function is_hot(a date, b date) returns int
11deterministic
12begin
13	declare d int;
14    set d := datediff(a, b);
15    return if(d > 60 or d < 3, 15, 0);
16end
17//
18
19create table test (
20	Passage int check(Passage > 0 and Passage < 100000),
21    Title varchar(40),
22    DepartureDate date,
23    DepartureTime time,
24    ArrivalDate date,
25    ArrivalTime time,
26    Seats bool,
27    Places varchar(50),
28    Passenger int check(Passenger > 0 and Passenger < 100000),
29    PassName varchar(40),
30    PurchaseDate date,
31    Passport varchar(20),
32    PassportDate date,
33    Region varchar(40),
34    Till int check(Till > 0 and Till < 100000),
35    Ticket bigint check(Ticket > 0 and Ticket < 10000000000),
36    Chair int check(Chair > 0 and Chair < 1000),
37    Class int check(Class > 0 and Class < 4),
38    -- Price
39    Registrar varchar(15),
40    Airliner bigint check(Airliner > 0 and Airliner < 10000000000),
41    AirlinerName varchar(15),
42    AirlinerYear int check(AirlinerYear > 1000 and AirlinerYear < 9999),
43	AirlinerPicture mediumblob,
44    Amount int check(Amount >= 0 and Amount < 10000)
45);
46
47insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class, Amount) values ('2015-06-01', '00:00:00', '2015-05-01', 1, 3, 17);
48insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class) values ('2015-06-01', '00:00:00', '2015-05-01', 1, 2);
49insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class) values ('2017-06-01', '00:00:00', '2017-03-01', 1, 3);
50insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class) values ('2015-06-01', '00:00:00', '2015-05-01', 1, 2);
51insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class) values ('2015-06-01', '00:00:00', '2015-05-01', 1, 1);
52
53select Passage, Title, DepartureDate, DepartureTime,
54ArrivalDate, ArrivalTime, Seats, Places, Passenger,
55PassName, PurchaseDate, Passport, PassportDate,
56Region, Till, Ticket, Chair, Class, ((4 - Class) * 4000 - ((4 - Class) * 40) * is_hot(PurchaseDate, DepartureDate)) as price,
57Registrar, Airliner, AirlinerName, AirlinerYear,
58(select count(*)
59	from test as testp
60    where Airliner = testpar.Airliner and (testp.DepartureDate < testpar.DepartureDate or testp.DepartureDate = testpar.DepartureDate and  testp.DepartureTime < testpar.DepartureTime)
61    order by Airliner, DepartureDate, DepartureTime
62) + (select min(Amount)
63	from test as testc
64    where testc.Airliner = testpar.Airliner
65    order by Airliner, DepartureDate, DepartureTime
66) as Amount
67from test as testpar
68order by Airliner, DepartureDate, DepartureTime;