MySQL/PHP 消除 MySQL 自连接中的重复、不相同的返回

Question

我有一个小型数据库，包含不到 400 匹小马的详细信息。我希望查询该表并返回一个显示每匹小马的相关详细信息及其所有者和饲养员姓名的表。数据主要是这样保存的：

1. 配置文件 - 一个表，保存分配给每匹小马的所有信息，包括它的父亲和母亲的注册号，以及它的所有者和饲养员的数据库分配的 ID。
2. 联系人 - 人员信息表。在下面的查询中作为“所有者”加入，并再次作为“饲养员”加入。
3. prm_* - 多个参数表，包含广泛的详细信息，例如颜色、品种等。

我遇到麻烦的是在尝试第一次自连接时：查询配置文件表三次以检索父亲和母亲的名称每个个人资料，以及小马自己的名字。当我运行查询时，它会返回许多（不是全部）配置文件的重复行。使用 DISTINCT 消除了其中的大部分，但问题仍然在于结果不一致，特别是对于那些没有父亲或母亲记录的小马。

我已经用谷歌搜索了这个问题，它确实出现这里和那里，但我不太清楚给出的解决方案中发生了什么。我什至不确定为什么会出现这个问题。有人可以指导我解决这个问题吗？我将不胜感激。

我的查询按目前的情况（仅从 387 匹小马返回 408 个结果！）：

include 'conn.php';
        ?>
<table class="admin-display">
<thead><tr><th>No:</th><th>Name:</th><th>Sire:</th><th>Dam:</th><th>Age:</th><th>Colour:</th><th>Gender:</th><th>Owner:</th><th>Breeder:</th></tr></thead>  
<?php
$i=1;

$sql = mysql_query("SELECT DISTINCT p.ProfileID, p.ProfileOwnerID, p.ProfileBreederID, p.ProfilePrefix, p.ProfileSireReg, p.ProfileDamReg,
                p.ProfileGenderID, p.ProfileAdultColourID, p.ProfileColourModifierID, p.ProfileYearOfBirth, 
                p.ProfileYearOfDeath, p.ProfileLocalRegNumber, p.ProfileName,
                sire.ProfileName AS sireName, sire.ProfilePrefix AS sirePrefix,
                dam.ProfileName AS damName, dam.ProfilePrefix AS damPrefix,
                owner.ContactFirstName AS owner_fname, owner.ContactLastName AS owner_lname,
                breeder.ContactFirstName AS breeder_fname, breeder.ContactLastName AS breeder_lname,
                BreedGender, BreedColour, BreedColourModifier
                FROM profiles AS p
                    LEFT JOIN profiles AS sire
                        ON p.ProfileSireReg = sire.ProfileLocalRegNumber
                    LEFT JOIN profiles AS dam
                        ON p.ProfileDamReg = dam.ProfileLocalRegNumber
                            LEFT JOIN contacts AS owner
                                ON p.ProfileOwnerID = owner.ContactID
                            LEFT JOIN contacts AS breeder
                                ON p.ProfileBreederID = breeder.ContactID
                    LEFT JOIN prm_breedgender
                                ON p.ProfileGenderID = prm_breedgender.BreedGenderID
                            LEFT JOIN prm_breedcolour
                                ON p.ProfileAdultColourID = prm_breedcolour.BreedColourID
                            LEFT JOIN prm_breedcolourmodifier
                                ON p.ProfileColourModifierID = prm_breedcolourmodifier.BreedColourModifierID
                          WHERE p.ProfileName != 'Unknown'
                          ORDER BY p.ProfileID ASC");

while($row = mysql_fetch_array($sql)) {

    $id = $row['ProfileID'];
    $name = $row['ProfilePrefix'] . ' ' . $row['ProfileName'];
    if ($row['ProfileYearOfDeath'] > 0000) { $age = ($row['ProfileYearOfDeath'] - $row['ProfileYearOfBirth']); }
    elseif ($row['ProfileYearOfDeath'] <= 0000) { $age = (date('Y') - $row['ProfileYearOfBirth']); }
    $reg = $row['ProfileLocalRegNumber'];
    $sire = $row['sirePrefix'] . ' ' . $row['sireName'];
    $dam = $row['damPrefix'] . ' ' . $row['damName'];
    $colour = $row['BreedColour'];
    $gender = $row['BreedGender'];
    $owner = $row['owner_fname'] . ' ' . $row['owner_lname'];
    $breeder = $row['breeder_fname'] . ' ' . $row['breeder_lname'];

    echo '<tr><td>' . $i++ . '</td><td>' . $name . '</td><td>' . $sire . '</td>';
    echo '<td>' . $dam . '</td><td>' . $age . '</td><td>' . $colour . '</td><td>' . $gender. '</td>';
    echo '<td>' . $owner . '</td><td>' . $breeder. '</td></tr>';
}
echo '</table>';

mysql_close($con);

Answer 1

使用GROUP BY而不是DISTINCT：

http://msmvps.com/blogs/robfarley/archive/2007/03/24/group-by-v-distinct-group-by-wins.aspx

Answer 2

问题出在数据中 - 您要连接的表之一有多行与连接键关联。

我建议分阶段执行查询。从基本查询开始（取出字段列表）：

SELECT count(*)
    FROM profiles AS p
    WHERE p.ProfileName != 'Unknown'

然后一次添加一个连接表，直到看到计数增加......

SELECT count(*) 
    FROM profiles AS p
    LEFT JOIN profiles AS sire
        ON p.ProfileSireReg = sire.ProfileLocalRegNumber
    WHERE p.ProfileName != 'Unknown'

然后您应该能够看到重复项在哪里。如果您想轻松查看哪条记录重复，可以运行以下查询：

SELECT p.Profile_id, count(*) cnt
    FROM profiles AS p
    LEFT JOIN profiles AS sire
        ON p.ProfileSireReg = sire.ProfileLocalRegNumber
    -- (all other joins)
    WHERE p.ProfileName != 'Unknown'
    GROUP BY p.Profile_id
    HAVING count(*) > 1

然后您可以查看重复记录的详细信息。