StoneHaos

Untitled

May 27th, 2020
116
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. -- MySQL
  3.  
  4. create database if not exists avia;
  5. use avia;
  6. drop table if exists test;
  7. drop function if exists is_hot;
  8.  
  9. delimiter //
  10. create function is_hot(a date, b date) returns int
  11. deterministic
  12. begin
  13.     declare d int;
  14.     set d := datediff(a, b);
  15.     return if(d > 60 or d < 3, 15, 0);
  16. end
  17. //
  18.  
  19. create 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.  
  47. insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class, Amount) values ('2015-06-01', '00:00:00', '2015-05-01', 1, 3, 17);
  48. insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class) values ('2015-06-01', '00:00:00', '2015-05-01', 1, 2);
  49. insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class) values ('2017-06-01', '00:00:00', '2017-03-01', 1, 3);
  50. insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class) values ('2015-06-01', '00:00:00', '2015-05-01', 1, 2);
  51. insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class) values ('2015-06-01', '00:00:00', '2015-05-01', 1, 1);
  52.  
  53. select Passage, Title, DepartureDate, DepartureTime,
  54. ArrivalDate, ArrivalTime, Seats, Places, Passenger,
  55. PassName, PurchaseDate, Passport, PassportDate,
  56. Region, Till, Ticket, Chair, Class, ((4 - Class) * 4000 - ((4 - Class) * 40) * is_hot(PurchaseDate, DepartureDate)) as price,
  57. Registrar, 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
  67. from test as testpar
  68. order by Airliner, DepartureDate, DepartureTime;
RAW Paste Data