mysql - MySQL总和不适用于单行结果

我有这个查询

SELECT product.product_id,
                    product.product_category_id,
                    product.product_subcategory_id,
                    product.product_brand_id,
                    product.product_model_id,
                    product.product_retail_price,
                    product.product_wholesale_price,
                    product.product_quoted_price,
                    product.product_aquoted_price,
                    product.product_dquoted_price,
                    (SELECT IFNULL(SUM(product_sold.product_quantity), 0) FROM product_sold WHERE product_sold.product_id = product.product_id
                    AND product_sold.product_sold_approved = 1
                    AND product_sold.product_sold_approved_time > $start_timestamp) AS num_product_sold,
                    product_brand.brand_name,
                    product_model.model_name,
                    product_subcategory.subcategory_name,
                    (SELECT IFNULL(SUM(product_stock.product_quantity),0) FROM product_stock WHERE product_stock.product_id = product.product_id) AS num_product_stock
    FROM product
        INNER JOIN product_brand
            ON product_brand.brand_id = product.product_brand_id
        INNER JOIN product_model
            ON product_model.model_id = product.product_model_id AND product_model.year_from <= $year AND product_model.year_to >= $year
        INNER JOIN product_subcategory
            ON product_subcategory.subcategory_id = $subcategory
        GROUP BY product.product_id
        ORDER BY num_product_sold DESC, num_product_stock DESC, product_brand.brand_name, product_model.model_name

问题是，当产品库存在库存表上只有一行时，它返回0，我将在之后执行此查询，但是问题是我需要按库存数量进行订购。即使产品在数据库中只有一行，有什么办法可以求和？

最佳答案

我已经尝试了您提到的内容，但没有发现任何问题：

    -- create tables
    create table product(
        id number not null,
        name varchar2(50) not null,
        quantity number default 0 not null);

    create table product_sold(
        id number not null,
        product_id number not null,
        quantity number default 0 not null);

    -- insert some data
    insert into product values(1, 'porsche', 55);
    insert into product values(2, 'bmw', 18);
    insert into product values(3, 'airbus A330', 2);


    insert into product_sold values(100,1, 3);
    insert into product_sold values(101,1, 15);
    insert into product_sold values(102,2, 6);

    commit;

    -- start querying

    SELECT product.id,product.name,(SELECT COALESCE(SUM(ps.quantity), 0) FROM product_sold ps WHERE ps.product_id = product.id) sum_sold
    FROM  product;

ID NAME SUM_SOLD
保时捷18 1
2宝马6
3架空中客车A330 0

关于mysql - MySQL总和不适用于单行结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22927784/