时间戳

首页 > 解决方案 > 添加一个新字段，通过表mysql的结果减去子查询的数量

mysql - 添加一个新字段，通过表mysql的结果减去子查询的数量

问题描述

我希望能够在子查询中创建一个新列，该列从给定 id 中减去一个来自另一个表的查询的金额，该表可以返回几条记录。

这是子查询：

+---------------------------------------+------------+----------+
| name                                  | id_product | quantity |
+---------------------------------------+------------+----------+
| Samsung Galaxy J3 2017                |         12 |        0 |
| Samsung Galaxy S8                     |         15 |        2 |
| Samsung Galaxy S7                     |         17 |        0 |
| Samsung Galaxy S7 Edge                |         18 |        5 |
| Samsung Galaxy J1 mini Prime Dual SIM |         20 |        1 |
+---------------------------------------+------------+----------+

SELECT pl.name, pl.id_product, psa.quantity as stock 
FROM `ps_product_lang` pl 
LEFT JOIN `ps_product` p ON pl.id_product = p.id_product
LEFT JOIN `ps_category_product` pc ON p.id_product = pc.id_product
LEFT JOIN `ps_stock_available` psa ON p.id_product = psa.id_product
WHERE pc.id_category = 13 AND p.id_category_default = 17 AND p.condition = 'new'
GROUP BY p.id_product

另一个查询可以显示一个或多个记录：

其他表

+------------+-------+
| id_product | total |
+------------+-------+
|         15 |     1 |
|         18 |     2 |
+------------+-------+
SELECT id_product, count(id_product) as total  
FROM ventacompra_tmp 
GROUP BY id_product;

我想要的结果是这样的，只减去结果来自另一个表的字段，其他字段保持不变，框架中的“”字段发生变化。

+---------------------------------------+------------+----------+-------+
| name                                  | id_product | quantity | stock |
+---------------------------------------+------------+----------+-------+
| Samsung Galaxy J3 2017                |         12 |        0 |     0 |
| Samsung Galaxy S8                     |         15 |        2 |   "1" |
| Samsung Galaxy S7                     |         17 |        0 |     0 |
| Samsung Galaxy S7 Edge                |         18 |        5 |   "3" |
| Samsung Galaxy J1 mini Prime Dual SIM |         20 |        1 |     1 |
+---------------------------------------+------------+----------+-------+

标签： mysqlsubquery

解决方案

如果我理解正确，只需LEFT JOIN在两个不同的结果集（派生表）上使用id_product.

现在减去stock左表中的值是否不是 NULL。使用以下内容：

SELECT t1.name, 
       t1.id_product, 
       t1.stock AS quantity,  
       (t1.stock - IFNULL(t2.total, 0)) AS stock 
FROM 
( SELECT pl.name, pl.id_product, psa.quantity as stock 
  FROM `ps_product_lang` pl 
  LEFT JOIN `ps_product` p ON pl.id_product = p.id_product
  LEFT JOIN `ps_category_product` pc ON p.id_product = pc.id_product
  LEFT JOIN `ps_stock_available` psa ON p.id_product = psa.id_product
  WHERE pc.id_category = 13 AND p.id_category_default = 17 AND p.condition = 'new'
  GROUP BY p.id_product ) AS t1 
LEFT JOIN 
  ( SELECT id_product, count(id_product) as total  
    FROM ventacompra_tmp 
    GROUP BY id_product ) AS t2 ON t2.id_product = t1.id_product

推荐阅读