MySQL 查找两个表之间库存水平的差异并返回结果 [英] MySQL Find differences in stock levels between two tables and return the result

问题描述

我有两个表格，第一个是昨天的库存水平，称为昨天"，包含两列:

I have two tables, the first is yesterdays stock levels called "yesterday" and contains two columns:

StockNumber, StockLevel

第二个表是今天的库存水平，称为今天"，具有相同的列StockNumber, StockLevel".

The second table is today's stock levels called "today" and has the same columns "StockNumber, StockLevel".

我的问题是，对于StockLevel"更改和新StockNumber" 出现在今天"中.

My question is, how do I find the differences in stock levels between the "yesterday" and "today" tables for both "StockLevel" changes and new "StockNumber" appearing in the "today".

如果这可以用直接的 SQL 来完成那就太好了，但使用 PHP 添加额外的处理级别也是可以的

If this can be done with straight SQL that is great, but adding an extra level of processing using PHP is also OK

推荐答案

select y.StockNumber, t.StockLevel - y.StockLevel from yesterday as y inner join today as t on (y.StockNumber = t.StockNumber)

捕获今天表中不在昨天表中的产品:

to capture products in the today table which weren't in the yesterday table:

select y.StockNumber, t.StockLevel - coalesce(y.StockLevel,0) from yesterday as y right join today as t on (y.StockNumber = t.StockNumber)

合并确保如果您的产品不在 yesterday 中，您将获得 t.StockLevel - 0 而不是 t.StockLevel- NULL

The coalesce ensures that if you have a product which isn't in yesterday, you'll get t.StockLevel - 0 instead of t.StockLevel - NULL

这篇关于MySQL 查找两个表之间库存水平的差异并返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！