sql - 根据聚合值选择单个记录

Question

假设我有下表

EmployeeID            Salary                      Date
-----------           ----------------            -----------
37                    45000.00                    2015-03-11 
102                   36500.00                    2015-03-11 
103                   43000.00                    2015-03-11 
104                   45000.00                    2015-03-11 
105                   40000.00                    2015-03-11 
37                    45000.00                    2015-04-11 
102                   36500.00                    2015-04-11 
103                   43000.00                    2015-04-11 
104                   45000.00                    2015-04-11 
105                   40000.00                    2015-04-11 

我想检索“Sum Of Salary”超过80000的记录，所以需要的输出是

EmployeeID            Salary                      Date
-----------           ----------------            -----------
37                    45000.00                    2015-03-11 
103                   43000.00                    2015-03-11 
104                   45000.00                    2015-03-11 
37                    45000.00                    2015-04-11 
103                   43000.00                    2015-04-11 
104                   45000.00                    2015-04-11 

我是通过以下方式实现的:

1. 将聚合结果存储到表变量中。

2. 原始表和表变量之间的内部连接

   DECLARE @tmpAggregatedSalaries TABLE
   (
       EmployeeID          INT,
       SumOfSalary         DECIMAL(18, 2)
   )
   
   INSERT INTO @tmpAggregatedSalaries
   SELECT sal.EmployeeID
       , SUM(sal.Salary) AS SumOfSalary
   FROM Salaries sal
   GROUP BY sal.EmployeeID
   
   SELECT sal.*
       FROM Salaries sal
       INNER JOIN @tmpAggregatedSalaries aggrSal ON sal.EmployeeID = aggrSal.EmployeeID
           AND aggrSal.SumOfSalary > 80000
   

据我所知，存储临时结果以进行处理优于内联查询，因此我选择了“表变量”。请给我建议更优化的版本。

Answer 1

一种选择是使用 GROUP BY 子查询来识别薪水总和大于 80000 的所有员工，以从您的表中过滤掉您不想看到的记录。

SELECT t1.EmployeeID,
       t1.Salary,
       t1.Date
FROM Salaries t1
INNER JOIN
(
    SELECT EmployeeID
    FROM Salaries
    GROUP BY EmployeeID
    HAVING SUM(Salary) > 80000
) t2
    ON t1.EmployeeID = t2.EmployeeID