加入SQL Server的聚合子查询

I am trying to compare the values of 2 tables to make sure they match. In one table some rows are split into smaller rows so these need to be grouped before they can be joined with their equal match in the other table. I know this isn't the best design but it is what i have been given.

我试图比较2个表的值，以确保它们匹配。在一个表中，一些行被分成较小的行，因此在它们可以与另一个表中的相等匹配连接之前，需要对它们进行分组。我知道这不是最好的设计，但它是我得到的。

This is my current logic

这是我目前的逻辑

SELECT *  
FROM Table2 T2
FULL JOIN
    (SELECT 
         MIN(No1) AS [No1],
         MIN(No2) AS [No2],
         SUM(Amount) AS [Amount]
     FROM 
         Table1
     GROUP BY 
         No1) T1 ON T1.No1 = T2.No1 AND T1.No2 = T2.No2

The result:

结果：

| No1 | No2 | Amount |    No1 |    No2 | Amount |
+-----+-----+--------+--------+--------+--------+
| 111 | 222 |     20 |    111 |    222 |     20 |
| 222 | 444 |     50 |    222 |    444 |     50 |
| 333 | 222 |     10 |    333 |    222 |     30 |
| 333 | 444 |     20 | (null) | (null) | (null) |

I don't understand how the amount for 333-222 on the right side is 30 when I would expect it to be 10. What am I missing here? Thanks

我不明白右边的333-222的数量是多少是30，当我期望它是10.我在这里想念的是什么？谢谢

SQL FIDDLE - http://sqlfiddle.com/#!18/e6e4c/4

2 个解决方案

#1

Seems like you want to group by both the columns: GROUP BY No1, No2

看起来你想要按列分组：GROUP BY No1，No2

#2

I don't get your confusion. You have in table1:

我不会让你感到困惑。你在table1中：

('333', '222' , 10),
('333', '444' , 20);

When aggregated by col1, the sum is "30". That is the "30" in the result set.

当由col1聚合时，总和为“30”。这是结果集中的“30”。

Perhaps you intend for the subquery to aggregate by both col1 and col2.

也许您打算通过col1和col2聚合子查询。

#1