php - PHP的加入和计数，然后分组和排序

我有两张桌子；
用户

id | username   | club
-----------------------
1  | James5     | 2
2  | 007        | 1
3  | xmen       | 2
4  | terminator | 2

建议者

 id | username | club
----------------------
 1  | mark     |  2
 2  | bon      |  1
 3  | hero     |  2
 4  | scorpio  |  2
 5  | lame     |  5

我该如何加入这些表格才能得到俱乐部的总数？例如

 club   |  clubCount
 -------------------
  2     |  6
  1     |  2
  5     |  1

我在想下面的问题；

SELECT User.club, COUNT(User.club) + COUNT(suggestedusers.club) AS clubCount FROM User, suggestedusers
GROUP BY User.club
ORDER BY clubCount DESC

但上面的脚本不起作用。

最佳答案

你的目标结果我不清楚。
你可以试试这个

 Select club, count(club) as clubcount from(
      select users.id, users.username, users.club from users
       UNION
     select suggestedusers.id, suggestedusers.username,
        suggestedusers.club from suggested users ) group by club, clubcount Order by
     clubcount desc;