mysql - SUM 结果的子查询 AVG

Question

我在子查询方面遇到问题。 所以我想列出总工作时间大于所有员工平均工作总时间的员工。 但使用此代码，它将一名员工的工作时间与另一名员工的每个小时的工作时间进行比较。 所以这是总结: 员工总工作时间 > 所有员工总工作时间的平均值 --> WHERE hrs_worked > AVG(SUM(HRS_WORKED)) 这就是我想要的。

SELECT employee.emp_id,
       employee.emp_name,
       SUM(action.hrs_worked) AS "Total Hours Worked"
FROM action
INNER JOIN allocation ON action.act_id = allocation.act_id
AND action.emp_id = allocation.emp_id
INNER JOIN employee ON allocation.emp_id = employee.emp_id
WHERE action.hrs_worked >
    (SELECT AVG(action.hrs_worked)
     FROM action)
GROUP BY employee.emp_id,
         employee.emp_name
ORDER BY SUM(action.hrs_worked) DESC

Answer 1

应该是这样的:

SELECT * 
FROM  Orders
........... 
WHERE Price > (
           SELECT AVG(sum_pr) 
           FROM (
              SELECT SUM(Price) as sum_pr 
              FROM Orders 
              GROUP BY Price
                 )x
               )