在一个 mysql 查询中获取男性/女性/所有人的平均投票评级

Question

我有以下头痛......我试图一次获得三个不同子句的平均评级，我想也许使用 UNION 命令会起作用，但它只会从第一个查询（要求平均评级的查询）中找到值。 averageMaleRating 和averageFemaleRating 作为未知列返回...

这是查询！

SELECT
  averageRating,
  averageMaleRating,
  averageFemaleRating
FROM (
  SELECT
    question_id AS q_id,
    ROUND(AVG(rating)) AS averageRating
  FROM
    wp_ratings
  WHERE
    club_id = ?
  GROUP BY question_id

  UNION

  SELECT
    question_id AS q_id,
    ROUND(AVG(rating)) AS averageMaleRating
  FROM
    wp_ratings
  WHERE
    club_id = ?
  AND
    GENDER = 'male'
  GROUP BY question_id

  UNION

  SELECT
    question_id AS q_id,
    ROUND(AVG(rating)) AS averageFemaleRating
  FROM
    wp_ratings
  WHERE
    club_id = ?
  AND
    GENDER = 'female'
  GROUP BY question_id
 )AS bigU
JOIN
  wp_ratings
ON
  wp_ratings.question_id = bigU.q_id

Answer 1

您使用 UNION 语句完全错误。

您需要从问题表中选择并加入每个性别的评分表

SELECT
  q.id,
  AVG(t.rating) AS avgTotal,
  AVG(m.rating) AS avgMale,
  AVG(f.rating) AS avgFemale
FROM questions AS q

LEFT JOIN ratings AS t
ON q.id = t.question_id

LEFT JOIN ratings AS m
ON q.id = m.question_id AND m.gender = 'male'

LEFT JOIN ratings AS f
ON q.id = f.question_id AND f.gender = 'female'

GROUP BY q.id