php - 使用 PHP 和 MySql 匹配两个表中的重复条目

Question

我有两个表，每个表有四列。两者都可能包含重复的行。我想将匹配的条目存储在一个表中，而将不匹配的条目存储在另一表中。这是我正在尝试的代码，但没有得到所需的输出。

<?php
    $con=mysqli_connect("localhost","root","","truck");
    // Check connection
    if (mysqli_connect_errno()){
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $sqlA="select date, truckno, bagscount from vendor";
    $sqlB="select date, truckno, bagscount from truck";
    $resultA=mysqli_query($con,$sqlA);
    $resultB=mysqli_query($con,$sqlB);
    $objA= mysqli_fetch_all($resultA,MYSQLI_NUM);
    $objB= mysqli_fetch_all($resultB,MYSQLI_NUM);
    $i=0;
    $j=0;
    while ($i<=(mysqli_num_rows($resultA)-1)){
        if ($objA[$i][0]=$objB[$j][0] && $objA[$i][1]=$objB[$j][1] && $objA[$i][2]=$objB[$j][2]){
            $i++;
            echo "row ". $i. " matches". $j. "<hr>";
            $j=0;
        }
        else 
        {
            echo "not matched!";
            $j++;
        }


    }
?>

Answer 1

查找匹配的行:

SELECT
    `vendor`.`date`,
    `vendor`.`truckno`,
    `vendor`.`bagscount`
FROM `vendor`
    INNER JOIN `truck`
        ON `vendor`.`date`=`truck`.`date`
            AND `vendor`.`truckno`=`truck`.`truckno`
            AND `vendor`.`bagscount`=`truck`.`bagscount`

查找 vendor 中不在 truck 中的行

SELECT
    `vendor`.`date`,
    `vendor`.`truckno`,
    `vendor`.`bagscount`
FROM `vendor`
    LEFT JOIN `truck`
        ON `vendor`.`date`=`truck`.`date`
            AND `vendor`.`truckno`=`truck`.`truckno`
            AND `vendor`.`bagscount`=`truck`.`bagscount`
WHERE
    `truck`.`date` IS NULL

查找 truck 中不在 vendor 中的行

SELECT
    `truck`.`date`,
    `truck`.`truckno`,
    `truck`.`bagscount`
FROM `truck`
    LEFT JOIN `vendor`
        ON `truck`.`date`=`vendor`.`date`
            AND `truck`.`truckno`=`vendor`.`truckno`
            AND `truck`.`bagscount`=`vendor`.`bagscount`
WHERE
    `vendor`.`date` IS NULL

左连接(最后两个)假设日期永远不为空。

您还可以通过在选择之前添加 INSERT INTO table_name SELECT ...{rest of select query}... 来插入另一个表。