MySQL 语法问题，多个 SELECT 语句

Question

我正在处理一个包含 2 个表的项目，如下所示:users_fb 和 posts

我花了 3 个小时玩代码，然后我放弃了。

表:帖子

+-----+---------+---------+---------+---------+-----------+
| id  | by_user | by_page |  votes  | status  |    time   |
+-----+---------+---------+---------+---------+-----------+
|   1 |       1 |       0 |      20 |       1 | 372041014 |
+-----+---------+---------+---------+---------+-----------+

表:users_fb

+-----+-----------+-------+---------+--------+-------+
| id  | username  | name  | gender  | fb_id  | email |
+-----+-----------+-------+---------+--------+-------+

---

SELECT username, 
        (
        SELECT      COUNT(b.by_user)
        FROM        users_fb a LEFT JOIN posts b ON a.id = b.by_user
        WHERE       b.by_page = '0'
        GROUP BY    a.username
        ) AS totalCount ,
        (
        SELECT      IFNULL(SUM(b.votes),0)
        FROM        users_fb a LEFT JOIN posts b ON a.id = b.by_user
        GROUP BY    users_fb.id
        ) AS total_votes
        FROM users_fb ORDER BY  total_votes DESC

期望的输出

+-------------------+-------------+-------------+
|     username      | totalCount  | total_votes |
+-------------------+-------------+-------------+
| user4             |          1  |          25 |
| user1             |          0  |          0  |
| user2             |          0  |          0  |
| user3             |          0  |          0  |
+-------------------+-------------+-------------+

不幸的是:这就是我得到的

+-------------------+-------------+-------------+
|     username      | totalCount  | total_votes |
+-------------------+-------------+-------------+
| user4             |          1  |          25 |
| user1             |          1  |          25 |
| user2             |          1  |          25 |
| user3             |          1  |          25 |
+-------------------+-------------+-------------+

如果您需要任何进一步的信息，请告诉我。感谢您的帮助。

Answer 1

你似乎没有任何东西可以将你的表格连接在一起以匹配帖子/投票给哪个用户。

应该是这样的

SELECT users_fb.username, Sub1.postcount, Sub2.votecount
FROM users_fb
LEFT OUTER JOIN(
    SELECT      a.username, COUNT(*) AS postcount
    FROM        users_fb a 
    INNER JOIN posts b 
    ON a.id = b.by_user
    WHERE       b.by_page = '0'
    GROUP BY    a.username
) Sub1
ON users_fb.username = Sub1.username
LEFT OUTER JOIN(        
    SELECT      users_fb.id, IFNULL(SUM(b.votes),0) AS votecount
    FROM        users_fb a 
    LEFT JOIN posts b 
    ON a.id = b.by_user
    GROUP BY    users_fb.id
) Sub2
ON users_fb.id = Sub2.id

可能简化为

SELECT      a.username, SUM(IF(b.by_page = '0', 1, 0)) AS postcount, IFNULL(SUM(b.votes),0) AS votecount
FROM        users_fb a 
LEFT JOIN posts b 
ON a.id = b.by_user
GROUP BY    a.username