SQL 带有 GROUP BY 和聚合函数的多个 INNER JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20561335/
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
Multiple INNER JOIN with GROUP BY and Aggregate Function
提问by chris_techno25
I'm back with another question. I've been tinkering with this for 1 and a half days now and still no luck. So I have the tables below.
我回来回答另一个问题。我已经修修补补了 1 天半了，但仍然没有运气。所以我有下面的表格。
Table1
Field1 Field2 Field3 Field4 Field5
DR1 500 ID1 Active TR1
DR2 250 ID2 Active TR1
DR3 100 ID1 Active TR1
DR4 50 ID3 Active TR1
DR5 50 ID1 Cancelled TR1
DR6 150 ID1 Active TR2
Table2
Field1 Field3
ID1 Chris
ID2 John
ID3 Jane
Table3
Field1 Field2
TR1 Shipped
TR2 Pending
I currently can achieve this result.
我目前可以达到这个结果。
Name Total
Chris 650 3
John 250 1
Jane 50 1
using this sql statement
使用这个sql语句
SELECT t2.Field3 as Name , SUM(t1.Field2) as Total
FROM [Table1] t1 INNER JOIN [Table2] t2 ON t1.Field3 = t2.Field1
GROUP BY t2.Field3
However, I'd like to achieve this result shown below.
但是，我想实现如下所示的结果。
Chris 600 2
John 250 1
Jane 50 1
I'd like to check Table3 first if it has a 'Shipped' Field2 then it includes everything in Table1 with 'Active' Field4. It should not include 'Cancelled' Field4. And if Table3 has a Field2 of Pending, it should also not include it. I'd appreciate any little help. Thank you.
我想首先检查 Table3，如果它有一个“已发货”Field2，那么它包含 Table1 中的所有内容和“Active”Field4。它不应包括“已取消”字段 4。如果 Table3 有一个 Pending 的 Field2，它也不应该包含它。我很感激任何一点帮助。谢谢你。
回答by Rapha?l Althaus
Well, you just need to join all the tables required to have the fields needed in the where clause.
好吧，您只需要连接所有需要 where 子句中需要的字段的表。
And add a whereclause.
并添加一个where条款。
SELECT t2.Field3,
SUM(t1.Field2) as CTotal,
count(*) as NbItems
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.Field3 = t2.Field1
--add a join on Table3
INNER JOIN Table3 t3
ON t3.Field1 = t1.Field5
--add your where clause
WHERE t1.Field4 = 'Active'
AND t3.Field2 <> 'Pending'
GROUP BY t2.Field3
--ORDER BY CTotal DESC
see SqlFiddle
EDIT
编辑
For your problem
对于您的问题
SELCT custInfo.CName,
Count(*) as TransactionCount,
SUM(CTotal) as TransactionTotal
FROM (TamarawTransaction trans
INNER JOIN TamarawCustomerInformation custInfo
ON trans.CCustomerCode=custInfo.CCustomerCode)
INNER JOIN TamarawTrip trip
ON trans.CTrip=trip.CTrip
Where trip.CStatus='Finalized'
AND trans.CStatus='Active'
GROUP BY custInfo.CName
With "weird access join syntax"
使用“奇怪的访问连接语法”
SELECT TamarawCustomerInformation.CName,
Count(*) AS TransactionCount,
SUM(CTotal) AS TransactionTotal
FROM TamarawCustomerInformationn
INNER JOIN (TamarawTrip INNER JOIN TamarawTransaction ON TamarawTrip.CTrip=TamarawTransaction.CTrip)
ON TamarawCustomerInformationn.CCustomerCode=TamarawTransaction.CCustomerCode
WHERE TamarawTrip.CStatus='" & "Shipped" & "' and TamarawTransaction.CStatus='" & "Active" & "'
GROUP BY TamarawCustomerInformation.CName;
