| Autor | Zpráva | ||
|---|---|---|---|
| Netolicak Profil * |
#1 · Zasláno: 28. 10. 2010, 17:15:09
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 |
#2 · Zasláno: 28. 10. 2010, 17:37:21
Netolicak: tie id_mincena a id_maxcena tam potrebuješ načo?
|
||
| Kajman_ Profil * |
#3 · Zasláno: 28. 10. 2010, 18:30:56
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 * |
#4 · Zasláno: 28. 10. 2010, 22:30:10
Kajman_: Funguje to na první pokus. Děkuji Vám. :)
|
||
|
Časová prodleva: 13 let
|
|||
0