MYSQL如何找到与当前存储产品匹配的订单产品（比较两个表以获得100%匹配）

Question

我正在寻找解决方案，以检查是否多行从一个表有匹配在其他表。在我的情况下，我需要检查订单中的项目是否在存储中。目前，我使用php检查订单-script将打开的订单和foreach一个接一个地检查存储。它产生了相当多的查询，这是不是效率在所有，我正在寻找解决方案，以做这通过sql查询。

期望的结果应该是:

OrderId | Date       | Products 

1002/02 | 2020/08/16 | 1x Ipod; 2x battery; 9x some item;

0333/4  | 2020/06/22 | 10x shelf

存储产品表

id | id_product | quantity

订单

id | reference | id_status | created_at

订购产品

Id | id_order | quantity | id_storage_product

我已经写了一些代码来生成上面可见的表，但是结果它甚至没有接近期望。

select('orders.id', orders.created_at','orders.reference', 'storage_products.id as storageProductId')
                ->join('order_products', 'orders.id', '=', 'order_products.id_order')
                ->join('storage_products', 'order_products.id_product', '=', 'storage_products.id_product')
                ->where('storage_products.quantity', '>=', 'order_products.quantity')
                ->whereIn('orders.id_status', array(1, 2))  //get new orders/ open
                ->where('order_products.id_storage_product', null) 
                ->groupBy('orders.id');

清除SQL:

SELECT `orders`.`id`,
       `orders`.`created_at`,
       `orders`.`reference`,
       `storage_products`.`id` AS `storageProductId`,
       `order_products`.`id_order`
FROM   `orders`
       INNER JOIN `order_products`
               ON `orders`.`id` =
                  `order_products`.`id_order`
       INNER JOIN `storage_products`
               ON `order_products`.`id_product` =
                  `storage_products`.`id_product`
WHERE  `storage_products`.`quantity` >=
              'order_products.quantity'
       AND `orders`.`id_status` IN ( 1, 2 )
       AND `order_products`.`id_storage_product` IS NULL
GROUP  BY `orders`.`id`
ORDER  BY `orders`.`id` ASC

因此代码应该查找未结订单(id_status)；储存数量等于或大于产品顺序的；其中id_storage_products为空（表示在网站上购买的产品，但在订购时未存储）。上查询结果是错误的，因为它显示了与存储部分匹配--即使没有检查数量（有些产品有0，但仍然显示）。

如有任何帮助，多谢

编辑:fiddle示例:https://www.db-fiddle.com/f/6jkvkxpyvslexgm3qv1nhu/0

Answer 1

我在db-fiddle链接上尝试了下面的查询，结果成功了。

SELECT
    orders.reference, orders.created_at, order_products.id_product
FROM
    storage_products 
LEFT JOIN 
    order_products ON storage_products.id_product = order_products.id_product 
LEFT JOIN
    orders ON orders.id = order_products.id_order;

我在查询中所做的是用order_products中相同的id_product调用所有的storage_products，然后继续调用所调用的order_products中的所有订单。

Answer 2

您的查询包含以下条件:

AND `order_products`.`id_storage_product` IS NULL

但在示例数据中，所有值都是0。
因此我使用coalesce()来覆盖这两种情况。
还删除了条件:

AND `orders`.`id_status` IN ( 1, 2 )

因为列ID_status不包括在示例数据中表orders的定义中。

此查询有效:

SELECT o.id,
       o.reference,
       o.created_at,
       GROUP_CONCAT(op.quantity, 'x', op.id_product separator ' ;') products
FROM orders o
INNER JOIN order_products op ON o.id = op.id_order
INNER JOIN storage_products sp ON op.id_product = sp.id_product
WHERE sp.quantity >= op.quantity AND COALESCE(op.id_storage_product, 0) = 0
GROUP  BY o.id, o.reference, o.created_at
ORDER  BY o.id ASC

请参阅演示。
结果:

| id  | reference | created_at          | products      |
| --- | --------- | ------------------- | ------------- |
| 2   | 345554/02 | 2020-08-22 00:00:00 | 3x188 ; 1x155 |

如果您还加入了表products（我假设有这样一个表），您可以获得产品的名称，而不是它们的ID。