代码之家 › 专栏 › 技术社区 › Andrew

MySQL:如何选择和显示一个表中的所有行,并计算另一个表上的WHERE子句的和?

join database mysql sql

Andrew · 技术社区 · 14 年前

我试图显示一个表中的所有行,并在一列中求和/平均结果,这是WHERE子句的结果。这可能没什么意义,所以让我解释一下。

我需要显示所有员工的报告…

SELECT Employees.Name, Employees.Extension 
FROM Employees;

--------------
| Name | Ext |
--------------
| Joe  | 123 |
| Jane | 124 |
| John | 125 |
--------------

…并从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     |
------------------------------------------------------------

现在我想过滤掉sum和avg计算中包含的一些行…

WHERE PhoneCalls.StartTime BETWEEN "2010-09-12 09:30:00" AND NOW()

…理想情况下,这会导致一张表看起来像这样:

------------------------------------------------------------
| Name | Ext | CallCount | AverageCallTime | TotalCallTime |
------------------------------------------------------------
| Joe  | 123 |      5    |       200       |      1000     |
| Jane | 124 |     10    |       250       |      2500     |
| John | 125 |      0    |         0       |         0     | 
------------------------------------------------------------

请注意,John没有在此日期范围内进行任何调用,因此他的总调用计数为零,但他仍在结果列表中。我好像不知道怎么把约翰的记录放在名单上。当我添加WHERE子句时,这些记录被过滤掉。

如何创建一个select语句,该语句只显示从where子句返回的值的sums/avg?

1 回复 | 直到 14 年前

OMG Ponies 14 年前

用途:

   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

问题在于使用外部联接时,将应用联接部分中的指定条件之前连接发生了——像派生表或内联视图。这个 WHERE 子句是在外部联接之后应用的,这就是为什么当您在要保留外部联接的表上指定WHERE子句时,仍然希望看到的行将被筛选掉的原因。