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

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 希望这对您的查询有帮助

在此我添加了索引键以删除“Using where;Using join buffer (Block Nested Loop)”，并添加一个 匹配的行和您的查询性能现在是最好的。

查询:

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`)

INSERT INTO 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) VALUES (6, 'XYZ', 1, 27, 3, 'HIGH SCHOOL - A LEVEL', 1, 9);

在这里检查:http://sqlfiddle.com/#!9/c4103d/2