mysql - 查询中的平均值 - mysql

Question

我有这个查询

  SELECT salary
    FROM worker W
    JOIN single_user U ON u.users_id_user = W.single_user_users_id_user
    JOIN university_has_single_user US ON US.single_user_users_id_user = U.users_id_user
    JOIN course C ON C.id_course = US.course_id_course
    JOIN formation_area FA ON FA.id_formation_area = C.formation_area_id_formation_area
   WHERE FA.area = "Multimédia"
GROUP BY users_id_user

...给出了这个输出:

salary
--------
1400.00
800.00

如何计算此输出的平均值？如果我添加:

SELECT round(avg (salary), 0) 

...输出再次是 1400.00 和 800.00，而不是平均值(因为分组依据)。

Answer 1

用途:

 SELECT AVG(DISTINCT salary)
   FROM worker W
   JOIN single_user U ON u.users_id_user = W.single_user_users_id_user
   JOIN university_has_single_user US ON US.single_user_users_id_user = U.users_id_user
   JOIN course C ON C.id_course = US.course_id_course
   JOIN formation_area FA ON FA.id_formation_area = C.formation_area_id_formation_area
  WHERE FA.area = "Multimédia"

由于 salary 列未包含在聚合中，per the documentation, the values you see are arbitrary (can't be guaranteed 100% of the time) 。

通常，您需要一个派生表来获取不同值的平均值，但 MySQL's AVG supports using DISTINCT within it .