mysql - 动态表上作为列 MYSQL 的计数总和

Question

以下 mysql 查询将输出一列用户名以及每个用户名后面的一系列 5 列，这是 5 次计数的结果。

SELECT f.username, 
(SELECT COUNT(id) FROM sites s WHERE s.username = f.username) site_count,
(SELECT COUNT(id) FROM banners b WHERE b.username = f.username) banner_count,
(SELECT COUNT(id) FROM favorites fa WHERE fa.username = f.username) favorite_count,
(SELECT COUNT(id) FROM social_posts so WHERE so.username = f.username) social_count,
(SELECT COUNT(id) FROM stream_updates st WHERE st.username = f.username) stream_count
FROM members f
    WHERE f.username 
      IN (SELECT friend2 as username FROM list_friends WHERE friend1 = :user
      AND friend2 <> :user)
    OR f.username 
      IN (SELECT friend1 as username FROM list_friends  WHERE friend2 = :user
    AND friend1 <> :user) 
    OR f.username = :user

输出示例:

| username | site_count | banner_count | favorite_count | social_count | stream_count |
+----------+------------+--------------+----------------+--------------+--------------+
|  user1   |     7      |       12     |        3       |       5      |     11       |
|  user2   |     2      |        1     |        12      |       1      |     0        |
|  user3   |     5      |        7     |        4       |       9      |     25       |

我希望实现的结果是每行的总和:

| username | site_count | banner_count | favorite_count | social_count | stream_count |
+----------+------------+--------------+----------------+--------------+--------------+
|          |    14      |       20     |        19      |      15      |     36       |

Answer 1

只是添加一个总和来计数？

SELECT f.username, 
    SUM((SELECT COUNT(id) FROM sites s WHERE s.username = f.username)) site_count,
    SUM((SELECT COUNT(id) FROM banners b WHERE b.username = f.username)) banner_count,
    SUM((SELECT COUNT(id) FROM favorites fa WHERE fa.username = f.username)) favorite_count,
    SUM((SELECT COUNT(id) FROM social_posts so WHERE so.username = f.username)) social_count,
    SUM((SELECT COUNT(id) FROM stream_updates st WHERE st.username = f.username)) stream_count
FROM members f
WHERE f.username 
  IN (SELECT friend2 as username FROM list_friends WHERE friend1 = :user
  AND friend2 <> :user)
OR f.username 
  IN (SELECT friend1 as username FROM list_friends  WHERE friend2 = :user
AND friend1 <> :user) 
OR f.username = :user

如果需要每个用户的计数，只需在末尾添加Group By f.username