如何找到两个表的两个字段之间的差异 [英] How to find difference between two fields of two tables

问题描述

我有两个表A和B.两个表都有3个字段。两个表中的2个字段相同，一个是文章ID，另一个是成本。这两个字段在两个表中都很常见，数据也很常见。但表A中的第三个字段是'qty_should_be'，而表B中的第三个字段是'qty_present'。我想要两个字段之间的区别，即库存少于或多于它应该是多少



什么我试过了：



表A

ART0000024 180.00 100.00

ART0000024 1079.00 1000.00



表B

ART0000024 144 100.00

ART0000024 1099 1000.00

i have two tables A and B. Both tables have 3 fields. 2 fields in both tables are same as one is 'Article ID' and other is 'Cost'. These 2 fields are common in both tables and data is also common. but the third field in table A is 'qty_should_be' and in table B is 'qty_present'. I want the difference between both fields that how much stock is less or more than it should be

What I have tried:

table A
ART0000024 180.00 100.00
ART0000024 1079.00 1000.00

table B
ART0000024 144 100.00
ART0000024 1099 1000.00

推荐答案

尝试：

Try:

SELECT a.[Article ID], a.qty_should_be - b.qty_present
FROM TableA a
JOIN TableB b ON a.[Article ID] = b.[Article ID]

试试这个

Try this

DECLARE @A TABLE
(
ARTICLE_ID VARCHAR(10),
COST NUMERIC(10, 2),
qty_should_be INT
)

DECLARE @B TABLE
(
ARTICLE_ID VARCHAR(10),
COST NUMERIC(10, 2),
qty_present INT
)

INSERT INTO @A(ARTICLE_ID, COST, qty_should_be)
SELECT 'ART0000024', 180,	100
UNION ALL
SELECT 'ART0000024', 1079,	1000

INSERT INTO @B(ARTICLE_ID, COST, qty_present)
SELECT 'ART0000024', 144,	100
UNION ALL
SELECT 'ART0000024', 1099,	1000

SELECT A.Article_ID, Sum(qty_should_be) - Sum(Qty_Present)
FROM @A A
      LEFT OUTER JOIN @B  B ON A.Article_ID = B.Article_ID
group by A.Article_ID

这篇关于如何找到两个表的两个字段之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！