mysql 中一个表里有父子关系,Mysql Self Join在同一表中查找父子关系

Im trying to calculate the amount of money won by all the offspring of a male race horse (Sire) over a time period. Listed by the Sire with the most amount of money won.

I run the query and get the result Im after with one problem, I cant display the sires name, only their ID.

SELECT `horses`.`SireID` AS `SireID` , `horses`.`HorseName` AS `Sire Name`,

COUNT( `runs`.`HorsesID` ) AS `Runs` ,

COUNT(

CASE WHEN `runs`.`Finish` =1

THEN 1

ELSE NULL

END ) AS `Wins` ,

CONCAT( FORMAT( (

COUNT(

CASE WHEN `runs`.`Finish` =1

THEN 1

ELSE NULL

END ) / COUNT

( `runs`.`TrainersID` ) ) *100, 0 ) , '%'

) AS `Percent` ,

FORMAT( SUM( `runs`.`StakeWon` ) , 0 ) AS `Stakes`

FROM runs

INNER JOIN horses ON runs.HorsesID = horses.HorsesID

INNER JOIN races ON runs.RacesID = races.RacesID

WHERE `races`.`RaceDate` >= STR_TO_DATE( '2012,07,01', '%Y,%m,%d' )

AND `races`.`RaceDate` < STR_TO_DATE( '2012,07,01', '%Y,%m,%d' ) + INTERVAL 1

MONTH

AND `horses`.`SireID` <> `horses`.`HorsesID`

GROUP BY `horses`.`SireID`, `horses`.`HorseName`

ORDER BY SUM( `runs`.`StakeWon` ) DESC

Take a record in the horse table for example, a horse has a horsesID and they also have a sireID (their father). The sireID has an equivalent horsesID in another record in the same table as it is also a horse

Basically I need to map the horseName to the sireID.

I thought a self join would work.

`AND `horses`.`SireID` <> `horses`.`HorsesID``

but it doesn't return the correct Sire name corresponding to the SireID.

解决方案

You can do a JOIN on the table itself. Here's a simpler example:

SELECT Horses.HorseID, Horses.HorseName, Horses.SireID, b.HorseName as SireName

FROM Horses

LEFT JOIN Horses b ON (Horses.SireID = b.HorseID)

You can probably figure out how to add the conditions from here.