在同一张表中使用Mysql自连接查找父子关系

Question

我正在尝试计算一匹种公马（父亲）的所有后代在某个时间段内赢得的金额，按赢得金额最多的父亲排序。我运行查询并得到了想要的结果，但有一个问题，我无法显示父亲的名称，只能显示它们的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

以马表中的一条记录为例，一匹马有一个马匹ID和一个父亲ID（sireID）。该父亲ID在同一张表中的另一条记录中具有等效的马匹ID，因为它本身也是一匹马。

基本上我需要将马名映射到sireID。

我认为自连接会起作用。

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

但它没有返回与SireID相对应的正确父系名称。

- stemie

Answer 1

您可以在表本身上执行JOIN操作。以下是一个更简单的示例：

SELECT Horses.HorseID, Horses.HorseName, Horses.SireID, b.HorseName as SireName
FROM Horses
LEFT JOIN Horses b ON (Horses.SireID = b.HorseID)

你可能可以从这里开始添加条件。

- Stephen O'Flynn

---

好的，很棒，它有效了 :) 我没想到一个左连接可以用于这个，感谢启示。 - stemie

Answer 2

join horses sires on sires.HorsesID = horses.SireID

- ilan berci

---

没有sires表。Sires出现在马表中，以父子关系存在。SireID，HorseID，Horsename。 - stemie

我意识到没有 sires 表..我们正在重新连接到 horses 表，然后命名关联为："sires"。所以现在我们有两个关联到 horses 表..一个使用默认的 'horses'..另一个是 'sires'. - ilan berci

我不确定我该怎么做？我一直收到“未知列”错误。 - stemie

谢谢你的帮助，我接受了Stephen上面的答案，因为对于像我这样的新手来说更容易理解。 - stemie