试图了解更多关于子查询的信息。我正在寻找一种方法来减去和比较两个表。

  • 库存
  • 销售

  • 我的数据记录如下:

    库存:
    mysql> select store_id, product_id, sum(quantity) as inventory from inventories where store_id = 1 group by product_id;
    +----------+------------+-----------+
    | store_id | product_id | inventory |
    +----------+------------+-----------+
    |        1 |          8 |        24 |
    |        1 |         10 |         4 |
    |        1 |         14 |        24 |
    +----------+------------+-----------+
    3 rows in set (0.00 sec)
    

    销售
    mysql> select store_id, product_id, sum(quantity) as sales from sales where store_id = 1 group by product_id;
    +----------+------------+-------+
    | store_id | product_id | sales |
    +----------+------------+-------+
    |        1 |          8 |    12 |
    |        1 |         14 |     2 |
    |        1 |          8 |     1 |
    +----------+------------+-------+
    2 rows in set (0.00 sec)
    

    获得以下结果的正确子查询是什么?
    +----------+------------+-----------+-------+-----------+
    | store_id | product_id | inventory | sales | remaining |
    +----------+------------+-----------+-------+-----------+
    |        1 |          8 |        24 |    12 |        12 |
    |        1 |         14 |        24 |     2 |        22 |
    |        1 |          8 |        12 |     1 |        11 |
    +----------+------------+-----------+-------+-----------+
    

    最佳答案

    要实现所需的输出,您需要计算产品销售的运行总计。要获得有意义的数据,sales 表中的数据必须按时间顺序排列。因此,您至少还需要一个字段来对数据进行排序——它是时间戳还是 id 字段都没有关系。假设 sales 表中有一个 id 字段。这是一个查询以获取您所描述的内容:

    SELECT
        sales.id,
        sales.store_id,
        sales.product_id,
        inventories.quantity-IFNULL(SUM(sales_2.quantity), 0) as inventory,
        sales.quantity as sales,
        inventories.quantity-IFNULL(SUM(sales_2.quantity), 0) - sales.quantity as remaining
    FROM
        sales
            INNER JOIN
        inventories ON inventories.store_id = sales.store_id
            AND inventories.product_id = sales.product_id
            LEFT JOIN
        sales AS sales_2 ON sales_2.store_id = sales.store_id
            AND sales_2.product_id = sales.product_id
            AND sales_2.id < sales.id
    GROUP BY sales.id , sales.store_id , sales.product_id
    ORDER BY sales.id
    

    名为 salessales_2 表的第二个实例用于计算早期销售额的总和 ( sales_2.id<sales.id )

    您可以从 sales.id 子句中排除 select,但需要将其保留在 group byorder by 中。

    关于mysql - 如何使用mysql子查询减去库存和销售?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/28436485/

    10-13 00:49