php - MYSQL Join Query Multiple Tables 部分字段显示重复结果

Question

我在 mysql 查询中连接表，它给我重复的结果，因为其中一个表有 2 个结果。

这是我的查询。

$query_order = "
SELECT sfo.entity_id, sfo.status, sfo.created_at, sfo.customer_firstname, sfo.customer_lastname, sfo.customer_email, sfo.subtotal, sfo.shipping_amount, sfo.tax_amount, sfo.grand_total, sfo.total_paid, sfo.total_due, sfo.total_item_count,
sfoa.address_type, sfoa.firstname, sfoa.lastname, sfoa.street, sfoa.city, sfoa.region, sfoa.postcode, sfoa.telephone, sfoa.country_id,
sfoi.product_id, sfoi.name, sfoi.sku, sfoi.qty_ordered, sfoi.price, sfoi.original_price, sfoi.row_total, sfoi.additional_data as additional_data1,
sfst.track_number, sfst.carrier_code, sfst.title,
sfop.additional_data as additional_data2, sfop.amount_paid, sfop.amount_ordered

FROM sales_flat_order sfo

JOIN sales_flat_order_address sfoa
ON sfo.entity_id = sfoa.parent_id

JOIN sales_flat_order_item sfoi
ON sfo.entity_id = sfoi.order_id

JOIN sales_flat_shipment_track sfst
ON sfo.entity_id = sfst.order_id

JOIN sales_flat_order_payment sfop
ON sfo.entity_id = sfop.entity_id

WHERE sfo.increment_id = '" . $po . "'

LIMIT 0 , 30";

sales_flat_order_address 的 parent_id 有 2 个结果，因为其中一个是账单地址，另一个是送货地址。这两个字段都具有相同的 parent_id 值，这就是我要加入表格的原因。

所有表都与来自 sales_flat_order sfo 的相同值连接，该值是 entity_id 并且是该表的主键。

我如何才能确保其他表只从它们的字段中输出一次值，同时确保我仍然从 sales_flat_order_address 表中获得账单地址和送货地址？

我尝试了 GROUP BY，它确实消除了重复项，但它也从 sales_flat_order_address 表中消除了送货地址。

我应该用子查询来构造查询吗？我已经进行了大量搜索，但到目前为止我不确定如何处理我的特殊情况。

2016 年 5 月 7 日更新

为了显示账单地址和送货地址，并且不复制其他连接表中的其他字段，我必须做的是我只是将地址表连接两次，这样 while 循环只循环一次而不是两次。

所以我将其添加到我的SELECT

sfoab.address_type as billing, sfoab.firstname as bfirst, sfoab.lastname as blast, sfoab.street as bstreet, sfoab.city as bcity, sfoab.region as bregion, sfoab.postcode as bzip, sfoab.telephone as btel, sfoab.country_id as bcountry,
sfoas.address_type as shipping, sfoas.firstname as sfirst, sfoas.lastname as slast, sfoas.street as sstreet, sfoas.city as scity, sfoas.region as sregion, sfoas.postcode as szip, sfoas.telephone as stel, sfoas.country_id as scountry,

然后我加入了两次地址表

LEFT JOIN sales_flat_order_address sfoab
ON sfo.entity_id = sfoab.parent_id AND sfoab.address_type = 'billing'

LEFT JOIN sales_flat_order_address sfoas
ON sfo.entity_id = sfoas.parent_id AND sfoas.address_type = 'shipping'

然后最后在 while 循环中我的变量

$billing = ucfirst($row['billing']);
$billing_firstname = $row['bfirst'];
$billing_lastname = $row['blast'];
$billing_name = ucwords($billing_firstname . " " . $billing_lastname);
$billing_street = ucwords($row['bstreet']);
$billing_city = ucwords($row['bcity']);
$billing_region = $row['bregion'];
$billing_postcode = $row['bzip'];
$billing_telephone = $row['btel'];
$billing_country = $countries[$row['bcountry']];

$shipping = ucfirst($row['shipping']);
$shipping_firstname = $row['sfirst'];
$shipping_lastname = $row['slast'];
$shipping_name = ucwords($shipping_firstname . " " . $shipping_lastname);
$shipping_street = ucwords($row['sstreet']);
$shipping_city = ucwords($row['scity']);
$shipping_region = $row['sregion'];
$shipping_postcode = $row['szip'];
$shipping_telephone = $row['stel'];
$shipping_country = $countries[$row['scountry']];

其他人的建议是使用 GROUP BY 或 DISTINCT。当我使用这些方法时，它不会同时显示账单地址和送货地址。它只会显示帐单而不是送货地址。

当我关闭这些方法时，账单和运费都会显示，但它会导致其他表中的其他字段输出两次。

所以我找到的唯一解决方案是两次加入地址表。这样做我得到了我想要的。而且 while 循环不会循环两次来获取两种地址类型。

如果您知道更好或更有效的方法来完成此任务，请告诉我。我是 MySQL 查询和 while 循环的新手。

Answer 1

您可以尝试按唯一列分组:像这样吗？

<?php 
    //THE ONLY ADDITION HERE IS THE "GROUP BY" CLAUSE
    //OTHERWISE THIS IS YOUR OWN CODE WITH THE EXCEPTION OF FORMATTING...



        $query_order = "
            SELECT
            sfo.entity_id,
            sfo.status,
            sfo.created_at,
            sfo.customer_firstname,
            sfo.customer_lastname,
            sfo.customer_email,
            sfo.subtotal,
            sfo.shipping_amount,
            sfo.tax_amount,
            sfo.grand_total,
            sfo.total_paid,
            sfo.total_due,
            sfo.total_item_count,

            sfoa.address_type,
            sfoa.firstname,
            sfoa.lastname,
            sfoa.street,
            sfoa.city,
            sfoa.region,
            sfoa.postcode,
            sfoa.telephone,
            sfoa.country_id,

            sfoi.product_id,
            sfoi.name,
            sfoi.sku,
            sfoi.qty_ordered,
            sfoi.price,
            sfoi.original_price,
            sfoi.row_total,
            sfoi.additional_data as additional_data1,

            sfst.track_number,
            sfst.carrier_code,
            sfst.title,
            sfop.additional_data as additional_data2,
            sfop.amount_paid,
            sfop.amount_ordered



            FROM sales_flat_order_address sfoa,
                 sales_flat_order sfo



            JOIN sales_flat_order_item sfoi
            ON sfo.entity_id = sfoi.order_id

            JOIN sales_flat_order_item sfoi
            ON sfo.entity_id = sfoi.order_id

            JOIN sales_flat_shipment_track sfst
            ON sfo.entity_id = sfst.order_id

            LEFT JOIN sales_flat_order_payment sfop
            ON sfo.entity_id = sfop.entity_id

            WHERE sfo.increment_id = " . (int)$po . "

            GROUP BY sfo.entity_id

            LIMIT 0 , 30";

取决于你想要达到的目标； LEFT JOIN 可能会像这样派上用场:

$query_order = "
    SELECT
    sfo.entity_id,
    sfo.status,
    sfo.created_at,
    sfo.customer_firstname,
    sfo.customer_lastname,
    sfo.customer_email,
    sfo.subtotal,
    sfo.shipping_amount,
    sfo.tax_amount,
    sfo.grand_total,
    sfo.total_paid,
    sfo.total_due,
    sfo.total_item_count,

    sfoa.address_type,
    sfoa.firstname,
    sfoa.lastname,
    sfoa.street,
    sfoa.city,
    sfoa.region,
    sfoa.postcode,
    sfoa.telephone,
    sfoa.country_id,

    sfoi.product_id,
    sfoi.name,
    sfoi.sku,
    sfoi.qty_ordered,
    sfoi.price,
    sfoi.original_price,
    sfoi.row_total,
    sfoi.additional_data as additional_data1,

    sfst.track_number,
    sfst.carrier_code,
    sfst.title,
    sfop.additional_data as additional_data2,
    sfop.amount_paid,
    sfop.amount_ordered


    FROM sales_flat_order_address sfoa,
    sales_flat_order sfo                     


    LEFT JOIN sales_flat_order_item sfoi
    ON sfo.entity_id = sfoi.order_id

    LEFT JOIN sales_flat_shipment_track sfst
    ON sfo.entity_id = sfst.order_id

    LEFT JOIN sales_flat_order_payment sfop
    ON sfo.entity_id = sfop.entity_id

    WHERE sfo.increment_id = " . (int)$po . "

    GROUP BY sfo.entity_id

    LIMIT 0 , 30";

我希望这对您有所帮助...