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

Question

我希望能够在子查询中创建一个新列，该列从来自另一个表的查询的给定 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 |
+---------------------------------------+------------+----------+-------+

Answer 1

如果我理解正确，只需使用 id_product 对两个不同的结果集(派生表)执行LEFT JOIN。

如果左表中的值不为 NULL，现在减去 stock。使用以下内容:

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