Mysql Self Join在同一表中查找父子关系 [英] Mysql Self Join to find a parent child relationship in the same table

问题描述

我正在尝试计算一段时间内雄性赛马(后代)的所有后代赢得的金钱金额.由父亲列出，赢得最多的钱. 我运行查询并得到结果Im之后，我遇到一个问题，我无法显示父亲姓名，仅显示其ID.

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

例如，在马表中进行记录，一匹马有一个horsesID，而他们也有一个sireID(他们的父亲). sireID在同一表的另一条记录中具有等效的horsesID，因为它也是一匹马

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

基本上，我需要将horseName映射到sireID.

Basically I need to map the horseName to the sireID.

我认为自我加入会奏效.

I thought a self join would work.

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

但是它没有返回与SireID相对应的正确Sire名称.

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

推荐答案

您可以在表本身上进行JOIN操作.这是一个更简单的示例:

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.

这篇关于Mysql Self Join在同一表中查找父子关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！