合并SQL查询后order_total数值错误，请求排查原因

我有两个SQL查询，想合并成一个，但合并后的查询返回错误结果。比如客户ID=1的order_total在第二个查询里是100（数据库验证准确），但合并后变成了600，数值不符。

原查询1（客户基础信息） #

SELECT 
        c.customer_id, 
        c.customer_lastname, 
        c.customer_firstname, 
        c.customer_email, 
        c.customer_group_id, 
        country.country_name AS customer_country, 
        customer_county.county_name AS customer_county,
        customer_city.city_name AS customer_city, 
        ci.customer_street_address, 
        ci.customer_phone, 
        c.num_orders, 
        cc.company_name, 
        cc.company_vat_number, 
        cc.J, 
        country2.country_name AS company_country, 
        company_county.county_name, 
        company_city.city_name, 
        cc.company_street, 
        cc.company_zip, 
        cc.company_phone, 
        cc.company_fax, 
        cc.company_bankname, 
        cc.company_iban, 
        c.customer_external_ref 
    FROM 
        customer AS c 
            LEFT JOIN customer_address AS ci USING (customer_id)
            LEFT JOIN customer_company AS cc USING (customer_id)
            LEFT JOIN country_i18n AS country 
                ON ci.country_code=country.country_code 
            LEFT JOIN country_i18n AS country2 
                ON cc.country_code = country2.country_code 
            LEFT JOIN county AS customer_county 
                ON customer_county.county_id = ci.county_id 
            LEFT JOIN city AS customer_city 
                ON customer_city.city_id = ci.city_id 
            LEFT JOIN county AS company_county 
                ON company_county.county_id = cc.county_id 
            LEFT JOIN city AS company_city 
                ON company_city.city_id = cc.city_id 
    WHERE 
        1 = 1 
    GROUP BY 
        customer_id 
    ORDER BY 
        customer_id DESC 

原查询2（订单统计信息） #

SELECT 
        o.customer_id, 
        o.customer_firstname, 
        o.customer_lastname, 
        o.customer_email, 
        MIN(o.order_datetime) as first_order_date, 
        MAX(o.order_datetime) as last_order_date, 
        DATEDIFF(NOW(),  MAX(o.order_datetime)) as days_since_last_order, 
        COUNT(DISTINCT o.order_id) as orders_count, 
        SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0)) AS sold_products_count, 
        SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) AS returned_products_count, 
        ROUND(100 * SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) 
            / SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0))) as returned_products_percent, 
        SUM(o.order_total) as orders_total 
    FROM 
        `order_products` op 
            INNER JOIN `order` o 
                ON (op.order_id = o.order_id) 
    WHERE 
        shop_module_class IS NULL 
    GROUP by 
        o.customer_id 
    ORDER BY
        o.customer_id DESC

错误的合并查询 #

SELECT 
        c.customer_id, 
        c.customer_lastname, 
        c.customer_firstname, 
        c.customer_email, 
        c.customer_group_id, 
        MIN(o.order_datetime) as first_order_date, 
        MAX(o.order_datetime) as last_order_date, 
        DATEDIFF(NOW(), MAX(o.order_datetime)) as days_since_last_order, 
        COUNT(DISTINCT o.order_id) as orders_count, 
        SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0)) AS sold_products_count, 
        SUM(IF(op.product_id > 0, IF(product_quantity < 0, -1 * op.product_quantity, 0), 0)) AS returned_products_count, 
        ROUND(100 * SUM(IF(op.product_id > 0, IF(product_quantity < 0, -1 * op.product_quantity, 0), 0)) 
            / SUM(IF(op.product_id > 0, IF(product_quantity > 0, op.product_quantity, 0), 0))) as returned_products_percent, 
        SUM(o.order_total) as orders_total, 
        country.country_name AS customer_country, 
        customer_county.county_name AS customer_county, 
        customer_city.city_name AS customer_city, 
        ci.customer_street_address, 
        ci.customer_phone, 
        c.num_orders, 
        cc.company_name, 
        cc.company_vat_number, 
        cc.J, 
        country2.country_name AS company_country, 
        company_county.county_name, 
        company_city.city_name, 
        cc.company_street, 
        cc.company_zip, 
        cc.company_phone, 
        cc.company_fax, 
        cc.company_bankname, 
        cc.company_iban, 
        c.customer_external_ref     
    FROM 
        `order_products` AS op 
            LEFT JOIN `order` AS o USING (order_id) 
            LEFT JOIN customer AS c USING (customer_id) 
            LEFT JOIN customer_address AS ci USING (customer_id) 
            LEFT JOIN customer_company AS cc USING (customer_id) 
            LEFT JOIN country_i18n AS country 
                ON ci.country_code = country.country_code 
            LEFT JOIN country_i18n AS country2 
                ON cc.country_code = country2.country_code 
            LEFT JOIN county AS customer_county 
                ON customer_county.county_id = ci.county_id 
            LEFT JOIN city AS customer_city 
                ON customer_city.city_id = ci.city_id 
            LEFT JOIN county AS company_county 
                ON company_county.county_id = cc.county_id 
            LEFT JOIN city AS company_city 
                ON company_city.city_id = cc.city_id 
    WHERE 
        1 = 1 
    GROUP BY 
        customer_id 
    ORDER BY 
        customer_id DESC

问题根源 #

合并查询出错的核心原因是笛卡尔积重复计算：
一个订单对应多个order_products行（每个商品一行），直接把order_products和客户信息表连接时，每个客户的订单统计值（比如order_total）会被重复计算，重复次数等于该订单下的商品数量。比如一个订单有6个商品，order_total就会被累加6次，导致最终结果变成6倍。

正确的合并方式 #

应该先对订单统计数据进行聚合（也就是先执行第二个查询的逻辑，得到每个客户的唯一统计行），再将这个统计结果和客户基础信息表连接，避免重复计算。

方案1：使用子查询 #

SELECT 
    c.customer_id, 
    c.customer_lastname, 
    c.customer_firstname, 
    c.customer_email, 
    c.customer_group_id, 
    stats.first_order_date,
    stats.last_order_date,
    stats.days_since_last_order,
    stats.orders_count,
    stats.sold_products_count,
    stats.returned_products_count,
    stats.returned_products_percent,
    stats.orders_total,
    country.country_name AS customer_country, 
    customer_county.county_name AS customer_county,
    customer_city.city_name AS customer_city, 
    ci.customer_street_address, 
    ci.customer_phone, 
    c.num_orders, 
    cc.company_name, 
    cc.company_vat_number, 
    cc.J, 
    country2.country_name AS company_country, 
    company_county.county_name, 
    company_city.city_name, 
    cc.company_street, 
    cc.company_zip, 
    cc.company_phone, 
    cc.company_fax, 
    cc.company_bankname, 
    cc.company_iban, 
    c.customer_external_ref 
FROM 
    customer AS c 
        LEFT JOIN customer_address AS ci USING (customer_id)
        LEFT JOIN customer_company AS cc USING (customer_id)
        LEFT JOIN country_i18n AS country 
            ON ci.country_code=country.country_code 
        LEFT JOIN country_i18n AS country2 
            ON cc.country_code = country2.country_code 
        LEFT JOIN county AS customer_county 
            ON customer_county.county_id = ci.county_id 
        LEFT JOIN city AS customer_city 
            ON customer_city.city_id = ci.city_id 
        LEFT JOIN county AS company_county 
            ON company_county.county_id = cc.county_id 
        LEFT JOIN city AS company_city 
            ON company_city.city_id = cc.city_id 
        -- 连接预聚合的订单统计子查询
        LEFT JOIN (
            SELECT 
                o.customer_id, 
                MIN(o.order_datetime) as first_order_date, 
                MAX(o.order_datetime) as last_order_date, 
                DATEDIFF(NOW(),  MAX(o.order_datetime)) as days_since_last_order, 
                COUNT(DISTINCT o.order_id) as orders_count, 
                SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0)) AS sold_products_count, 
                SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) AS returned_products_count, 
                ROUND(100 * SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) 
                    / SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0))) as returned_products_percent, 
                SUM(o.order_total) as orders_total 
            FROM 
                `order_products` op 
                    INNER JOIN `order` o 
                        ON (op.order_id = o.order_id) 
            WHERE 
                shop_module_class IS NULL 
            GROUP by 
                o.customer_id
        ) AS stats USING (customer_id)
WHERE 
    1 = 1 
GROUP BY 
    c.customer_id 
ORDER BY 
    c.customer_id DESC 

方案2：使用CTE（MySQL 8.0+支持） #

如果你的数据库支持CTE，代码可读性会更好：

WITH customer_order_stats AS (
    SELECT 
        o.customer_id, 
        MIN(o.order_datetime) as first_order_date, 
        MAX(o.order_datetime) as last_order_date, 
        DATEDIFF(NOW(),  MAX(o.order_datetime)) as days_since_last_order, 
        COUNT(DISTINCT o.order_id) as orders_count, 
        SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0)) AS sold_products_count, 
        SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) AS returned_products_count, 
        ROUND(100 * SUM(IF(op.product_id > 0, IF(op.product_quantity < 0, -1 * op.product_quantity, 0), 0)) 
            / SUM(IF(op.product_id > 0, IF(op.product_quantity > 0, op.product_quantity, 0), 0))) as returned_products_percent, 
        SUM(o.order_total) as orders_total 
    FROM 
        `order_products` op 
            INNER JOIN `order` o 
                ON (op.order_id = o.order_id) 
    WHERE 
        shop_module_class IS NULL 
    GROUP by 
        o.customer_id
)
SELECT 
    c.customer_id, 
    c.customer_lastname, 
    c.customer_firstname, 
    c.customer_email, 
    c.customer_group_id, 
    stats.first_order_date,
    stats.last_order_date,
    stats.days_since_last_order,
    stats.orders_count,
    stats.sold_products_count,
    stats.returned_products_count,
    stats.returned_products_percent,
    stats.orders_total,
    country.country_name AS customer_country, 
    customer_county.county_name AS customer_county,
    customer_city.city_name AS customer_city, 
    ci.customer_street_address, 
    ci.customer_phone, 
    c.num_orders, 
    cc.company_name, 
    cc.company_vat_number, 
    cc.J, 
    country2.country_name AS company_country, 
    company_county.county_name, 
    company_city.city_name, 
    cc.company_street, 
    cc.company_zip, 
    cc.company_phone, 
    cc.company_fax, 
    cc.company_bankname, 
    cc.company_iban, 
    c.customer_external_ref 
FROM 
    customer AS c 
        LEFT JOIN customer_address AS ci USING (customer_id)
        LEFT JOIN customer_company AS cc USING (customer_id)
        LEFT JOIN country_i18n AS country 
            ON ci.country_code=country.country_code 
        LEFT JOIN country_i18n AS country2 
            ON cc.country_code = country2.country_code 
        LEFT JOIN county AS customer_county 
            ON customer_county.county_id = ci.county_id 
        LEFT JOIN city AS customer_city 
            ON customer_city.city_id = ci.city_id 
        LEFT JOIN county AS company_county 
            ON company_county.county_id = cc.county_id 
        LEFT JOIN city AS company_city 
            ON company_city.city_id = cc.city_id 
        LEFT JOIN customer_order_stats AS stats USING (customer_id)
WHERE 
    1 = 1 
GROUP BY 
    c.customer_id 
ORDER BY 
    c.customer_id DESC 

额外注意点 #

1. 原合并查询中存在字段歧义问题：SUM(IF(op.product_id > 0, IF(product_quantity < 0, -1 * op.product_quantity, 0), 0))里的product_quantity未加表别名op.，在多表连接时可能引发错误，已在正确方案中修正。
2. 如果需要包含没有订单的客户，使用LEFT JOIN连接统计子查询即可，和原查询1的逻辑保持一致。

内容的提问来源于stack exchange，提问作者user3848412