| Autor | Zpráva | ||
|---|---|---|---|
| blazej44800 Profil |
Ahojte
Mám takýto sql príkaz: SELECT ride.date AS date, ride.time AS time, ride.time_tenth AS time_tenth, ride.speed AS speed, model.name AS model, producer.name AS name, user.user AS user, ride.id AS ride_id, ride.user_id AS user_id, ride.vehicle_id AS vehicle_id, ride.length AS length, ride.length_id AS length_id FROM `rides` AS ride JOIN `vehicles` AS vehicle ON vehicle.id = ride.vehicle_id JOIN `vehicles_producers` AS producer ON producer.id = vehicle.producer JOIN `vehicles_models` AS model ON model.id = vehicle.model JOIN `users` AS user ON ride.user_id = user.id GROUP BY ride.user_id, ride.vehicle_id, ride.length, ride.length_id ORDER BY ride.`time` ASC, ride.`time_tenth` ASC Problém ale je že potrebujem výsledky najprv zoradiť podľa času (ride.time a ride.time_tenth) a následne použiť group by na zlúčenie podobných výsledkov. Neviete ako na to? Vďaka :) |
||
| Keeehi Profil |
#2 · Zasláno: 5. 7. 2012, 21:19:17
Není to náhodou problém podobný tomuto?
|
||
| blazej44800 Profil |
#3 · Zasláno: 5. 7. 2012, 22:37:24
Mohli by ste mi to niekto pomoct spravit? Sice som tomu prikladu vo FAQ #3 porozumel ale neviem ako to aplikovat na tolko joinov ako mam v prikaze ja
|
||
| Kajman Profil |
#4 · Zasláno: 5. 7. 2012, 22:46:33
blazej44800:
Aplikujte to to bez joinu jen na tabulku rides. Až výsledek teprve spojte s dalšími tabulkami. |
||
| blazej44800 Profil |
Tak podobny prikaz tohoto typu som ešte nevidel:
SELECT ordering.date AS date, ordering.time AS time, ordering.time_tenth AS time_tenth, ordering.speed AS speed, model.name AS model, producer.name AS name, user.user AS user, ordering.id AS ride_id, ordering.user_id AS user_id, ordering.vehicle_id AS vehicle_id, ordering.length AS length, ordering.length_id AS length_id
FROM `mototime_rides` AS ride,
(
SELECT min(rides.time) AS time, min(rides.time_tenth) AS time_tenth, rides.id AS id, rides.vehicle_id AS vehicle_id, rides.user_id AS user_id, rides.date AS date, rides.speed AS speed, rides.length AS length, rides.length_id AS length_id
FROM `rides` AS rides
JOIN `vehicles` AS vehicle ON vehicle.id = rides.vehicle_id
JOIN `vehicles_producers` AS producer ON producer.id = vehicle.producer
JOIN `vehicles_models` AS model ON model.id = vehicle.model
GROUP BY rides.user_id, rides.vehicle_id, rides.length, rides.length_id
) ordering
JOIN `vehicles` AS vehicle ON vehicle.id = ordering.vehicle_id
JOIN `vehicles_producers` AS producer ON producer.id = vehicle.producer
JOIN `vehicles_models` AS model ON model.id = vehicle.model
JOIN `users` AS user ON ordering.user_id = user.id
WHERE ride.id = ordering.id
ORDER BY ordering.time, ordering.time_tenthAle hlavne že funguje, len sa obávam o výkon databázy (ale zatiaľ to je fajn): Ukázať záznamy 0 - 4 (5 celkovo, Dopyt zabral 0.0002 sek.) Vďaka všetkým za pomoc :) |
||
| blazej44800 Profil |
Tak nakoniec este optimalizacia a je z toho toto:
SELECT ordering.producer AS producer, ordering.model AS model, ordering.ride_id AS ride_id, ordering.user AS user, ordering.time AS time, ordering.time_tenth AS time_tenth, ordering.speed AS speed, ordering.date AS date, ordering.vehicle_id AS vehicle_id, COUNT(ride.id) AS other_results
FROM `rides` AS ride,
(
SELECT producer.name AS producer, model.name AS model, ride.id AS ride_id, user.user AS user, ride.time AS time, ride.time_tenth AS time_tenth, ride.speed AS speed, ride.date AS date, vehicle.id AS vehicle_id
FROM `rides` AS ride
JOIN `vehicles` AS vehicle ON vehicle.id = ride.vehicle_id
JOIN `vehicles_producers` AS producer ON producer.id = vehicle.producer
JOIN `vehicles_models` AS model ON model.id = vehicle.model
JOIN `users` AS user ON ride.user_id = user.id
ORDER BY ride.time ASC, ride.time_tenth ASC
) AS ordering
WHERE ride.id = ordering.ride_id
GROUP BY ride.user_id, ride.vehicle_id, ride.length, ride.length_id
ORDER BY ride.time ASC, ride.time_tenth ASC |
||
|
Časová prodleva: 12 let
|
|||
0