我想要将一个表中的行与另一个表中的行进行匹配，并且每行应该只匹配一次

Question

我希望将一个表中的行与另一个表中的行进行匹配，并且每行应该只匹配一次。我构造了下面的查询，它在所有其他方面都工作得很好，除了我没有进一步调整它，使一行只能被选择一次。

try{$results_pref_school1 = $db->query('SELECT mps.mps_client_ec_no, mcs.mcs_client_ec_no, mps.mps_school_id, mcs.mcs_school_id
                                          FROM match_pref_schools AS mps
                                          INNER JOIN match_current_schools AS mcs
                                          ON mps.mps_school_id = mcs.mcs_school_id
                                          AND mcs.mcs_id IN (SELECT MIN(mcs.mcs_id) 
                                                              FROM match_current_schools AS mcs 
                                                              GROUP BY mcs.mcs_school_id)
                                          ORDER BY mcs.mcs_id');
}catch (Exception $e){
        echo 'Failed to retrieve matched preferred school';
        exit;

}
$matched_school = $results_pref_school1->fetchAll(PDO::FETCH_ASSOC);

$matched_school的var_dump会产生以下结果：

Array
(

[1] => Array
    (
        [mps_client_ec_no] => REG5
        [mcs_client_ec_no] => GL98888
        [mps_school_id] => 6
        [mcs_school_id] => 6
    )

[2] => Array
    (
        [mps_client_ec_no] => TAS4752
        [mcs_client_ec_no] => ALF1252
        [mps_school_id] => 14
        [mcs_school_id] => 14
    )

[3] => Array
    (
        [mps_client_ec_no] => MAP002
        [mcs_client_ec_no] => ALF1252
        [mps_school_id] => 14
        [mcs_school_id] => 14
    )

)

在上面的结果中，我希望ALF1252只匹配一次。

Answer 1

@Parts 希望这对您的查询有帮助

在这篇文章中，我为remove添加了索引键"Using where；Using join buffer (Block Best) "，并添加了一个匹配的行，现在您的查询性能最好。

查询：

SELECT mps.mps_client_ec_no,mps.mps_school_id, mcs.mcs_client_ec_no,  mcs.mcs_school_id 
                                          FROM match_pref_schools AS mps   INNER JOIN match_current_schools AS mcs
                                          ON mps.mps_school_id = mcs.mcs_school_id
                                          where mcs.mcs_id IN (SELECT MIN(mcs.mcs_id) 
                                                              FROM match_current_schools AS mcs 
                                                              GROUP BY mcs.mcs_school_id)
                                          ORDER BY mcs.mcs_id

索引：

KEY mcs-key (mcs\_school\_id、mcs\_id、mcs\_client\_ec\_no ) KEY mps-key (mps\_school\_id、mps\_client\_ec\_no)

插入match_current_schools (mcs_id，mcs_client_ec_no，mcs_school_id，mcs_distr_id，mcs_province_id，mcs_client_level_taught，mcs_sub1_id，mcs_sub2_id)值(6，'XYZ'，1，27，3，‘高中-A级’，1，9)；

点击这里：http://sqlfiddle.com/#!9/c4103d/2