比较表中2列值与第二表sql server中另一列值之和

Question

我有两张桌子。

table1 ( id，项目，价格)值：

id | item | price
-------------
10 | book | 20  
20 | copy | 30   
30 | pen  | 10

....table2 ( id，项目，价格)值：

id | item | price
-------------
10 | book | 20
10 | book | 30

现在，如果在表1中没有id-10、item-book和price-(20+30)的记录，那么我想在新表中插入带有sum(20+30)的行.

Answer 1

如果我没有误解您的要求，这应该可以做到：

 SELECT T2.ID, T2.ITEM,T2.SUMPRICE FROM 
 (SELECT ID, ITEM, SUM(PRICE) AS SUMPRICE FROM table1 GROUP BY ID, ITEM) AS T1
 INNER JOIN 
 (SELECT ID, ITEM, SUM(PRICE) AS SUMPRICE FROM table2 GROUP BY ID, ITEM) AS T2
 ON T1.ID = T2.id  AND T1.item = T2.item WHERE T1.SUMPRICE <> T2.SUMPRICE 

如果您的第三个表已经创建，您可以只使用一个插入SELECT语句。否则，您可以使用像这样的选择成：

SELECT T2.ID, T2.ITEM,T2.SUMPRICE as price into table3 FROM 
(SELECT ID, ITEM, SUM(PRICE) AS SUMPRICE FROM table1 GROUP BY ID, ITEM) AS 
T1
INNER JOIN 
(SELECT ID, ITEM, SUM(PRICE) AS SUMPRICE FROM table2 GROUP BY ID, ITEM) AS 
T2
ON T1.ID = T2.id  AND T1.item = T2.item WHERE T1.SUMPRICE <> T2.SUMPRICE 

希望能帮上忙！

如果要获取所有不匹配的行，则编辑1，即：

1. 表2中的行，其中id、项目或价格与表1中的给定行不同时匹配
2. 表2中没有一个列与表1中的给定行同时匹配的行。

您可以使用例外语句，例如：

(SELECT ID, ITEM, SUM(PRICE) AS SUMPRICE FROM table2 GROUP BY ID, ITEM) 
 EXCEPT
(SELECT ID, ITEM, SUM(PRICE) AS SUMPRICE FROM table1 GROUP BY ID, ITEM) 

这将返回：

ID   ITEM                 SUMPRICE
---- -------------------- -----------
10   book                 50

Answer 2

尝试以下几点

SELECT T2.id,T2.item,T2.Price as Table2_Price,T1.Price as Table1_Price FROM (
SELECT id,Item,Sum(Price) as Price
FROM Table2
Group BY id,Item ) AS T2 LEFT JOIN Table1 AS T1
ON T1.Item = T2.Item and T1.ID = T2.ID

在阅读了您的注释when sum of these two not equals the one in another table it should return the sum from table 2之后，您将请求以下逻辑

SELECT T2.id,T2.Item, CASE WHEN T1.Price IS NULL THEN T2.Price 
                           WHEN T2.Price <> T1.Price THEN T2.Price 
                           ELSE T1.Price END as Price FROM (
SELECT id,Item,Sum(Price) as Price
FROM Table2
Group BY id,Item ) AS T2 LEFT JOIN Table1 AS T1
ON T1.Item = T2.Item and T1.ID = T2.ID

但是这个逻辑是没有用的，因为如果Sum(Table2_price) <> Table1_Price想要选择Sum(Table2_price) else，而Sum(Table2_price) = Table1_Price需要Table1_Price !！所以你总是想选择Sum(Table2_Price)