MySQL：如何从一个表中选择和显示所有行，并计算另一个表上 where 子句的总和？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3917595/
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
MySQL: How to select and display ALL rows from one table, and calculate the sum of a where clause on another table?
提问by Andrew
I'm trying to display all rows from one table and also SUM/AVG the results in one column, which is the result of a where clause. That probably doesn't make much sense, so let me explain.
我试图显示一个表中的所有行，并在一个列中显示 SUM/AVG 结果，这是 where 子句的结果。这可能没有多大意义，所以让我解释一下。
I need to display a report of all employees...
我需要显示所有员工的报告...
SELECT Employees.Name, Employees.Extension
FROM Employees;
--------------
| Name | Ext |
--------------
| Joe | 123 |
| Jane | 124 |
| John | 125 |
--------------
...and join some information from the PhoneCalls table...
...并加入 PhoneCalls 表中的一些信息...
--------------------------------------------------------------
| PhoneCalls Table |
--------------------------------------------------------------
| Ext | StartTime | EndTime | Duration |
--------------------------------------------------------------
| 123 | 2010-09-05 10:54:22 | 2010-09-05 10:58:22 | 240 |
--------------------------------------------------------------
SELECT Employees.Name,
Employees.Extension,
Count(PhoneCalls.*) AS CallCount,
AVG(PhoneCalls.Duration) AS AverageCallTime,
SUM(PhoneCalls.Duration) AS TotalCallTime
FROM Employees
LEFT JOIN PhoneCalls ON Employees.Extension = PhoneCalls.Extension
GROUP BY Employees.Extension;
------------------------------------------------------------
| Name | Ext | CallCount | AverageCallTime | TotalCallTime |
------------------------------------------------------------
| Joe | 123 | 10 | 200 | 2000 |
| Jane | 124 | 20 | 250 | 5000 |
| John | 125 | 3 | 100 | 300 |
------------------------------------------------------------
Now I want to filter out some of the rows that are included in the SUM and AVG calculations...
现在我想过滤掉 SUM 和 AVG 计算中包含的一些行......
WHERE PhoneCalls.StartTime BETWEEN "2010-09-12 09:30:00" AND NOW()
...which will ideally result in a table looking something like this:
...理想情况下，这将导致表格看起来像这样：
------------------------------------------------------------
| Name | Ext | CallCount | AverageCallTime | TotalCallTime |
------------------------------------------------------------
| Joe | 123 | 5 | 200 | 1000 |
| Jane | 124 | 10 | 250 | 2500 |
| John | 125 | 0 | 0 | 0 |
------------------------------------------------------------
Note that John has not made any calls in this date range, so his total CallCount is zero, but he is still in the list of results. I can't seem to figure out how to keep records like John's in the list. When I add the WHERE clause, those records are filtered out.
请注意，John 在此日期范围内没有拨打任何电话，因此他的 CallCount 总数为零，但他仍在结果列表中。我似乎无法弄清楚如何在列表中保留像约翰这样的记录。当我添加 WHERE 子句时，这些记录会被过滤掉。
How can I create a select statement that displays all of the Employees and only SUMs/AVGs the values returned from the WHERE clause?
如何创建一个 select 语句来显示所有员工，并且只显示从 WHERE 子句返回的值的 SUM/AVG？
回答by OMG Ponies
Use:
用：
SELECT e.Name,
e.Extension,
Count(pc.*) AS CallCount,
AVG(pc.Duration) AS AverageCallTime,
SUM(pc.Duration) AS TotalCallTime
FROM Employees e
LEFT JOIN PhoneCalls pc ON pc.extension = e.extension
AND pc.StartTime BETWEEN "2010-09-12 09:30:00" AND NOW()
GROUP BY e.Name, e.Extension
The issue is when using an OUTER JOIN, specifying criteria in the JOIN section is applied beforethe JOIN takes place--like a derived table or inline view. The WHEREclause is applied after the OUTER JOIN, which is why when you specified the WHERE clause on the table being LEFT OUTER JOIN'd to that the rows you still wanted to see are being filtered out.
问题是在使用 OUTER JOIN 时，在 JOIN 部分中指定的条件会在JOIN 发生之前应用- 就像派生表或内联视图一样。该WHERE子句在 OUTER JOIN 之后应用，这就是为什么当您在被 LEFT OUTER JOIN 的表上指定 WHERE 子句时，您仍然希望看到的行被过滤掉。
