Autor Zpráva
Netolicak
Profil *
Dobrý den,

chtěl bych Vás požádat o radu, mám následující tabulku:


CREATE TABLE IF NOT EXISTS `lety` (
  `id` int(11) NOT NULL auto_increment,
  `date_time` datetime NOT NULL,
  `flight_number` int(11) NOT NULL,
  `location_from` varchar(3) collate utf8_unicode_ci NOT NULL,
  `location_to` varchar(3) collate utf8_unicode_ci NOT NULL,
  `price` double NOT NULL,
  `taxes` double NOT NULL,
  `multiplier` double NOT NULL,
  `company` varchar(50) collate utf8_unicode_ci NOT NULL,
  `saved` datetime NOT NULL,
  `totalPrice` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=134 


Ke kazdemu letu existuje nekolik cenovych variant ja potrebuji zjisti ke kazdemu letu nejdrazsi a nejlevnejsi cenu.

Chtel bych dosahnout tohoto:

id
date_time
flight_number
location_from
location_to
id_maxcena
maxcena
id_mincena
mincena

Vzdy se u porovnavanych letu musi shodovat date_time, flight_number, location_from, location_to

Zatím jsem vymyslel něco jako, ale bohuzel id a company zustava stale stejne:
SELECT t1.`date_time` , t1.`flight_number` , t1.`location_from` , t1.`location_to`, t1.minimum, t1.id, t1.company, t2.company, t2.id, t2.maximum

FROM (SELECT `date_time` , `flight_number` , `location_from` , `location_to`, MIN(`totalPrice`) as minimum, id, company
FROM `lety`
GROUP BY `date_time` , `flight_number` , `location_from` , `location_to`
ORDER BY totalPrice ASC) as t1

JOIN 
(SELECT `date_time` , `flight_number` , `location_from` , `location_to`, MAX(`totalPrice`) as maximum, id, company
FROM `lety`
GROUP BY `date_time` , `flight_number` , `location_from` , `location_to`
ORDER BY totalPrice DESC) AS t2 ON t1.flight_number = t2.flight_number
tiso
Profil
Netolicak: tie id_mincena a id_maxcena tam potrebuješ načo?
Kajman_
Profil *
Možná nějak takto... těžko říct, co tam má být za firmu, když jich má hraniční cenu víc.
SELECT t1.*,
       L1.id      minid,
       L1.company mincompay,
       L2.id      maxid,
       L2.company maxcompany
FROM   (SELECT date_time,
               flight_number,
               location_from,
               location_to,
               MIN(totalPrice) as minimum,
               MAX(totalPrice) as maximum
        FROM   lety
        GROUP  BY date_time, flight_number, location_from, location_to
        ORDER  BY totalPrice ASC) t1
JOIN   lety L1
ON     (t1.date_time, t1.flight_number, t1.location_from, t1.location_to, t1.minimum) = 
       (L1.date_time, L1.flight_number, L1.location_from, L1.location_to, L1.totalPrice)
JOIN   lety L2
ON     (t1.date_time, t1.flight_number, t1.location_from, t1.location_to, t1.maximum) = 
       (L2.date_time, L2.flight_number, L2.location_from, L2.location_to, L2.totalPrice)
GROUP  BY t1.date_time, t1.flight_number, t1.location_from, t1.location_to
Netolicak
Profil *
Kajman_: Funguje to na první pokus. Děkuji Vám. :)

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: