此组合预期结果的SQL查询-透视

Question

很抱歉问了这个冗长的问题，但我不知道如何构建我想要的结果所需的SQL查询。我将概述我目前运行和运行良好的两个查询，并概述我需要的结果。任何帮助都将不胜感激。

第一次查询：

SELECT c.name AS name, count(*) AS total, sum(a.views) AS total_views, sum(a.views) / count(*) as average_views
FROM table_a a
JOIN table_b b ON b.id = a.b_id
JOIN table_c c ON c.id = b.c_id
WHERE a.status = 0 AND a.type in (2, 4, 5)
GROUP BY c.name ORDER BY c.name;

结果：

--------------------------------------------
name | total | total_views | average_views |
--------------------------------------------
aaaa |     2 |         150 |            75 |
bbbb |     1 |          75 |            75 |
dddd |     1 |          25 |            25 |
--------------------------------------------

第二个查询：

SELECT c.name AS name, count(*) AS total, sum(a.views) AS total_views, sum(a.views) / count(*) as average_views
FROM table_a a
JOIN table_b b ON b.id = a.b_id
JOIN table_c c ON c.id = b.c_id
WHERE a.status = 0 AND a.type in (1, 3)
GROUP BY c.name ORDER BY c.name;

第二个结果：

--------------------------------------------
name | total | total_views | average_views |
--------------------------------------------
aaaa |     2 |         200 |           100 |
bbbb |     1 |         100 |           100 |
dddd |     1 |          25 |            25 |
--------------------------------------------

给定这些带有这些数据的表格：表Table_a:

-----------------------------------
id | b_id | views | type | status |
-----------------------------------
 1 |  100 |   100 |    2 |      0 |
 2 |  200 |    75 |    4 |      0 |
 3 |  300 |    50 |    5 |      0 |
 4 |  400 |    25 |    2 |      0 |
 5 |  500 |   100 |    1 |      0 |
 6 |  600 |   100 |    1 |      0 |
 7 |  700 |   100 |    3 |      0 |
 8 |  800 |    25 |    3 |      0 |
-----------------------------------

表Table_b:

-------------
id  | c_id  |
-------------
100 |  1000 |
200 |  2000 |
300 |  1000 |
400 |  4000 |
500 |  1000 |
600 |  2000 |
700 |  4000 |
800 |  1000 |
-------------

表Table_c:

-------------
id   | name |
-------------
1000 | aaaa |
2000 | bbbb |
3000 | cccc |
4000 | dddd |
-------------

这就是我真正想要的表，它只是上面两个表的连接，公共列是名称列。

-------------------------------------------------------------------------------------------------------------------------------
name | total_type245 | total_views_type245 | average_views_type245 | total_type13 | total_views_type13 | average_views_type13 |
-------------------------------------------------------------------------------------------------------------------------------
aaaa |             2 |                 150 |                    75 |            2 |                200 |                  100 |
bbbb |             1 |                  75 |                    75 |            1 |                100 |                  100 |
dddd |             1 |                  25 |                    25 |            1 |                 25 |                   25 |
-------------------------------------------------------------------------------------------------------------------------------

这很可能是一个非常简单的查询，但我不知道该怎么做

谢谢。

Answer 1

只需将结果连接在一起；

SELECT ResultsA.name, 
total_type245, 
total_views_type245, 
average_views_type245,
total_type13,
total_views_type13,
average_views_type13
FROM
(
    SELECT c.name AS name, count(*) AS total_type245, sum(a.views) AS total_views_type245, sum(a.views) / count(*) as average_views_type245
    FROM table_a a
    JOIN table_b b ON b.id = a.b_id
    JOIN table_c c ON c.id = b.c_id
    WHERE a.status = 0 AND a.type in (2, 4, 5)
    GROUP BY name
) as ResultsA
JOIN 
(
    SELECT c.name AS name, count(*) AS total_type13, sum(a.views) AS total_views_type13, sum(a.views) / count(*) as average_views_type13
    FROM table_a a
    JOIN table_b b ON b.id = a.b_id
    JOIN table_c c ON c.id = b.c_id
    WHERE a.status = 0 AND a.type in (1, 3)
    GROUP BY name
) as ResultsB ON ResultsA.name = ResultsB.name
ORDER BY ResultsA.name

Answer 2

好的，所以在Matt的帮助下，这个查询可以工作：

SELECT c.name, total_type245, total_views_type245, average_views_type245, total_type13, total_views_type13, average_views_type13
FROM table_c c
LEFT JOIN (
    SELECT c.name AS name, count(*) AS total_type245, sum(a.views) AS total_views_type245, sum(a.views) / count(*) as average_views_type245
    FROM table_a a
    JOIN table_b b ON b.id = a.b_id
    JOIN table_c c ON c.id = b.c_id
    WHERE a.status = 0 AND a.type in (2, 4, 5)
    GROUP BY name
) as ResultsA ON ResultsA.name = c.name
LEFT JOIN (
    SELECT c.name AS name, count(*) AS total_type13, sum(a.views) AS total_views_type13, sum(a.views) / count(*) as average_views_type13
    FROM table_a a
    JOIN table_b b ON b.id = a.b_id
    JOIN table_c c ON c.id = b.c_id
    WHERE a.status = 0 AND a.type in (1, 3)
    GROUP BY name
) as ResultsB ON ResultsB.name = c.name;

不过，这是对工作最有效的查询吗？我似乎重复了很多查询，唯一的变化是a.type值是差异。