MySQL - 将表 1 的列与表 2 和表 3 中两列的平均值连接

Question

我正在尝试合并以下 3 个表的列。

pages:
*id* | *identifier*   | reference  | url        | ...
-------------------------------------------------------
 1   | 1              | page one   | http://... | ...
 2   | 3              | page two   | .....      | ...
 3   | 23             | page three | .....      | ...
 4   | 25             | page four  | .....      | ...
 5   | 43             | page five  | .....      | ...

comments:
page_id | *rating* | comment   | is_approved | name | ...
-------------------------------------------------------
 1      | 4        | bla bla   | 1           | joe  | ...
 2      | 5        | more bla  | 1           | jim  | ...
 2      | 3        | blub      | 1           | jill | ...
 3      | 1        | blubblub  | 1           | jack | ...
 4      | 2        | hey ho    | 0           | jimbo| ...
 5      | 4        | huhu      | 1           | mike | ...

ratings: 
page_id | *rating* | ip_address | ...
-----------------------------------
 1      | 3        | ...        | ...
 1      | 2        | ...        | ...
 2      | 5        | ...        | ...
 3      | 4        | ...        | ...
 4      | 0        | ...        | ...
 5      | 2        | ...        | ...

pages.id 链接到 comments.page_id 和 ratings.page_id

更具体地说，我想获得 comments. rating 和 ratings. rating 的平均值作为“star_total”，并将这个新列与“pages”中的相应行组合起来，这样我就得到了这样的表结构: id、标识符、star_total。

这就是我一直在尝试的事情。我知道事情不应该如此。这只是一个粗略的想法，据我所知:

SELECT pages.id, pages.identifier, star_total 
FROM pages LEFT JOIN 
(
    SELECT AVG(`rating`) FROM (
        SELECT 'rating' FROM comments
        WHERE `comments.is_approved = '1'
            AND comments.rating != '0'
            AND comments.page_id = ratings.page_id
        UNION ALL
        SELECT `rating`
        FROM ratings
        WHERE ratings.page_id = comments.page_id
    ) AS `star_total`
)

Answer 1

我们可以做这样的事情:

    SELECT pages.id
         , pages.identifier
         , q.star_total
      FROM pages
      LEFT
      JOIN ( SELECT t.page_id
                  , SUM(t.tot_rating)/SUM(t.cnt_rating) AS star_total
               FROM ( SELECT c.page_id           AS page_id
                           , SUM(c.rating)       AS tot_rating
                           , COUNT(c.rating)     AS cnt_rating
                        FROM comments c
                       WHERE c.is_approved = '1'
                         AND c.rating != '0'
                       GROUP BY c.page_id
                       UNION ALL
                      SELECT r.page_id        -- AS page_id
                           , SUM(r.rating)    -- AS tot_rating
                           , COUNT(r.rating)  -- AS cnt_rating
                        FROM ratings r
                       GROUP BY r.page_id
                    ) t
              GROUP BY t.page_id
           ) q
        ON q.page_id = pages.id

对于大型集合，那些内联 View (派生表)将会非常昂贵。获得等效结果的稍微简单的方法可能会加剧内联 View 的性能问题:

    SELECT pages.id
         , pages.identifier
         , q.star_total
      FROM pages
      LEFT
      JOIN ( SELECT t.page_id
                  , AVG(t.rating) AS star_total
               FROM ( SELECT c.page_id           AS page_id
                           , c.rating            AS rating
                        FROM comments c
                       WHERE c.is_approved = '1'
                         AND c.rating != '0'
                       UNION ALL
                      SELECT r.page_id        -- AS page_id
                           , r.rating         -- AS rating 
                        FROM ratings r
                    ) t
              GROUP BY t.page_id
           ) q
        ON q.page_id = pages.id