php - 从 2 个表中添加具有相同名称的数量

Question

我有一个表格，其零件号包含 15 个字符，在这个字符串中，一个数字包含 4 个不同的特征。我正在对表进行查询，然后剥离出不同的数字字符串，然后在其他表中使用。我遇到的问题是当有多个具有相同值的子字符串时。然后我通过我的 Material list 表运行它以获得制造整个零件所需的数量。但是当我想让它返回所有重复项的总和时，它会返回重复项。

这就是我正在做的:

$result = mysql_query("SELECT * FROM $table WHERE `active` = '1'");
    while ($r = mysql_fetch_assoc($result)) {
        $bucket_back_num = substr($r['part_num'],0,7);
        $bucket_bottom_num = substr($r['part_num'],0,7);
        $hooks_part_num = substr($r['part_num'],7,11-7);
        $grapple_part_num = substr($r['part_num'],11,15-11);
        $bucket_back_num .= $grapple_part_num;

        $result2 = mysql_query("SELECT *,sum(qty) as qty from bucket_part_bom WHERE `parent_part_num` = '$bucket_back_num' GROUP BY parent_part_num");
        while ($r2 = mysql_fetch_assoc($result2)) {
            echo '
                <tr>
                    <td style="width: 15%;">'.$r2['part_num'].'</td>
                    <td style="width: 25%; text-align: left;">'.$r2['desc'].'</td>
                    <td style="width: 15%;">'.$r2['qty'].'</td>
                    <td style="width: 15%;">Unpainted</td>
                    <td style="width: 10%;">Diff</td>
                    <td style="width: 10%;">Build</td>
                    <td style="width: 10%;">N/A</td>
                </tr>
            ';
        }
    }

返回的内容:

LM102BRBK   Bucket Back 102" Light Material Bare    1
LM1023XBK   Bucekt Back 102" Light Material, GF3X   1
LM1023XBK   Bucekt Back 102" Light Material, GF3X   1

Answer 1

使用 JOIN 将两个查询组合成一个查询

SELECT t.*, b.*, SUM(b.qty) AS qty
FROM $table AS t
JOIN bucket_part_bom AS b ON b.parent_part_num = SUBSTR(t.part_num, 1, 7)
GROUP BY b.parent_part_num