尝试将 INNER JOIN 和 GROUP BY SQL 与 SUM 函数一起使用，但不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23031118/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working
提问by Rob4236
I am not getting my head around this, and wondered if anyone may be able to help me with this.
我不明白这个问题，想知道是否有人可以帮助我解决这个问题。
I have 2 Tables called RES_DATAand INV_DATA
我有 2 个表叫RES_DATA和INV_DATA
RES_DATAContains my Customer as below
RES_DATA包含我的客户如下
CUSTOMER ID | NAME
1, Robert
2, John
3, Peter
INV_DATAContains their INVOICES as Below
INV_DATA包含他们的发票如下
INVOICE ID | CUSTOMER ID | AMOUNT
100, 1, ￡49.95
200, 1, ￡105.95
300, 2, ￡400.00
400, 3, ￡150.00
500, 1, ￡25.00
I am Trying to write a SELECTSTATEMENT Which will give me the results as Below.
我正在尝试写一个SELECT语句，它会给我如下结果。
CUSTOMER ID | NAME | TOTAL AMOUNT
1, Robert, ￡180.90
2, John, ￡400.00
3, Peter, ￡150.00
I think I need 2 INNER JOINS Somehow to Add the tables and SUM Values of the INVOICES Table GROUPED BY the Customer Table but honestly think I am missing something. Can't even get close to the Results I need.
我想我需要 2 个内部联接以某种方式添加按客户表分组的发票表的表和总和值，但老实说，我认为我遗漏了一些东西。甚至无法接近我需要的结果。
回答by rory.ap
This should work.
这应该有效。
SELECT a.[CUSTOMER ID], a.[NAME], SUM(b.[AMOUNT]) AS [TOTAL AMOUNT]
FROM RES_DATA a INNER JOIN INV_DATA b
ON a.[CUSTOMER ID]=b.[CUSTOMER ID]
GROUP BY a.[CUSTOMER ID], a.[NAME]
I tested it with SQL Fiddle against SQL Server 2008: http://sqlfiddle.com/#!3/1cad5/1
我用 SQL Fiddle 针对 SQL Server 2008 对其进行了测试：http://sqlfiddle.com/#!3/1cad5/1
Basically what's happening here is that, because of the join, you are getting the same row on the "left" (i.e. from the RES_DATAtable) for every row on the "right" (i.e. the INV_DATAtable) that has the same [CUSTOMER ID]value. When you group by just the columns on the left side, and then do a sum of just the [AMOUNT]column from the right side, it keeps the one row intact from the left side, and sums up the matching values from the right side.
基本上这里发生的事情是，由于连接，对于具有相同值RES_DATA的“右侧”（即INV_DATA表）上的每一行，您都会在“左侧”（即从表中）获得相同的行[CUSTOMER ID]。当您仅按左侧的列分组，然后仅对[AMOUNT]右侧的列进行求和时，它会保持左侧的一行完整，并汇总右侧的匹配值。
回答by Hogan
Two ways to do it...
有两种方法可以做到...
GROUP BY
通过...分组
SELECT RES.[CUSTOMER ID], RES,NAME, SUM(INV.AMOUNT) AS [TOTAL AMOUNT]
FROM RES_DATA RES
JOIN INV_DATA INV ON RES.[CUSTOMER ID] INV.[CUSTOMER ID]
GROUP BY RES.[CUSTOMER ID], RES,NAME
OVER
超过
SELECT RES.[CUSTOMER ID], RES,NAME,
SUM(INV.AMOUNT) OVER (PARTITION RES.[CUSTOMER ID]) AS [TOTAL AMOUNT]
FROM RES_DATA RES
JOIN INV_DATA INV ON RES.[CUSTOMER ID] INV.[CUSTOMER ID]
回答by Hola Soy Edu Feliz Navidad
Use subquery
使用子查询
SELECT * FROM RES_DATA inner join (SELECT [CUSTOMER ID], sum([TOTAL AMOUNT]) FROM INV_DATA group by [CUSTOMER ID]) T on RES_DATA.[CUSTOMER ID] = t.[CUSTOMER ID]
SELECT * FROM RES_DATA inner join (SELECT [CUSTOMER ID], sum([TOTAL AMOUNT]) FROM INV_DATA group by [CUSTOMER ID]) T on RES_DATA.[CUSTOMER ID] = t.[CUSTOMER ID]
回答by chindirala sampath kumar
If you need to retrieve more columns other than columns which are in group by then you can consider below query. Check it once whether it is performing well or not.
如果您需要检索除 group by 列之外的更多列，则可以考虑以下查询。检查一次它是否表现良好。
SELECT
a.[CUSTOMER ID],
a.[NAME],
(select SUM(b.[AMOUNT]) from INV_DATA b
where b.[CUSTOMER ID] = a.[CUSTOMER ID]
GROUP BY b.[CUSTOMER ID]) AS [TOTAL AMOUNT]
FROM RES_DATA a
