Jump to content
php.lv forumi

group by izlaiž rezultātu


metal-0-1

Recommended Posts

Vajag palīdzību ar group by.

Ir tabula person un tabula vehicle, kas glabā info par personai piederošajiem transportlīdzekļiem.

 

person

id  |  username

1   |   bob

2   |   billy

3   |  john

 

vehicle

id | person_id | vehicle | cost  |

1      1                car         5000  

2      1                car         4000  

2      1                bike       1000

4      3                bike       500

 

Nepieciešams uzzināt, kāda ir vidējā katras personas automašīnu cena. Ja personai nepieder neviena automašīna, tad atgriež NULL. Sagaidu rezultātu:

 

name | avg_car

bob      4000

billy      NULL

john     NULL

 

Bet mans query neatgriež personu `john` vispār nemaz. http://sqlfiddle.com/#!2/5c195/2

 

Jo john pieder mocis, tātad pirmais where clause ir FALSE, bet arī otrais where clause ir FALSE, jo kaut kāds transportlīdzeklis viņam tomēr pieder.

Edited by metal-0-1
Link to comment
Share on other sites

Tavs kverijs ir pareizs, bet es nedaudz pielaboju netīšām. :D

 

~~~

SELECT

`person`.`name`,

ROUND(AVG(`vehicle`.`cost`), 2) AS `cost`

FROM `person`

LEFT JOIN `vehicle` ON `person`.`id` = `vehicle`.`person_id`

WHERE `vehicle`.`vehicle` = "car"

GROUP BY `person`.`id`

~~~

 

Protams, ka tavs kverijs neatgriež Džonu. Džonam nav nevienas mašīnas, tikai velo pa 100 bakšiem.

Link to comment
Share on other sites

SELECT
`person`.`name`,
IF(
`vehicle`.`id` IS NULL
OR `vehicle`.`vehicle` != "car",
0,
ROUND(AVG(`vehicle`.`cost`), 2)
) AS `cost`
FROM `person`
LEFT JOIN `vehicle` ON `person`.`id` = `vehicle`.`person_id`
GROUP BY `person`.`id`
Result:

 

bob     2833.33
billy   0
john    0
Atgriezt NULL tur, kur ir paredzēts skaitlis, nav smuki, nemiksē kopā dažādus datu tipus. Ja vajag, atgriez -1 vai 0. Edited by jurchiks
Link to comment
Share on other sites

To query iespējams uzrakstīt daudz vienkāršāku un ātrāku (sqlfiddlā uz tiem ierakstiem, 2ms pret 31ms)

SELECT 
`person`.`name`, 
round(avg(`vehicle`.`cost`)) AS `avg_car` 

FROM `person` LEFT OUTER JOIN `vehicle` 
     ON `person`.`id` = `vehicle`.`person_id` 
          AND `vehicle`.`vehicle` = 'car'
 
GROUP BY `person`.`id`
Edited by xPtv45z
Link to comment
Share on other sites

Starp citu, tikko stackoverflowā uzgāju šādu baigi vienkāršu, labi izskaidrotu pamācību par SQL JOINiem: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Pašā apakšā ir cheat sheet.

Mans query builderis šo to no tā neatbalsta, būs jāpilnveido.

Edited by jurchiks
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...