mysql - 计票以及用户是否在 MySQL 表中投票

Question

我有 3 个 MySQL 表:

特征表:

id    name
----------
 1    feature 1
 2    feature 2
 3    feature 3
 4    feature 4
 5    feature 5

投票表:

userid featureid
----------------
1       1
1       2
1       3
1       4
2       1
2       2
2       3
2       4
3       1
3       2
3       3
4       1

用户表:

id  name
--------
 1  John
 2  Alice
 3  Bob
 4  Mark
 5  Jane
 6  Mary
 7  Ann

我需要在单个查询中获取:

• 总是所有特征，无论他们是否有投票
• 无论投票数多少，每个特征都必须只列出一次，即使它没有投票也是如此
• 列出的每个功能的投票数
• 如果当前登录的用户为列表中的当前功能投票，则为特殊标记 - 例如，如果用户 3 已登录，则列出所有功能以及所有用户的投票计数，如果用户 3 为某些功能投票，则有一个特殊字段指示他的投票，如果他没有投票，则为 NULL(投票表中的其他数据也必须包括在内，因此需要左连接)

到目前为止我是这样做的:

SELECT *,(SELECT COUNT(*) FROM votes WHERE votes.featureid=features.id) AS "votecnt"
FROM features
LEFT JOIN votes ON votes.featureid=features.id
LEFT JOIN users ON users.id=votes.userid
GROUP BY features.id

如果用户 3 投票，它会列出所有功能，但没有特殊字段。我尝试了 IF、各种 WHERE 条件，并在尝试了很多次之后……没有想法。

期望的输出可能如下所示:

features.id  features.name  votes.userid  votes.otherfields  users.id  users.name
     1         feature 1          4             -                4        Mark
     2         feature 2        NULL            -              NULL       NULL
     3         feature 3        NULL            -              NULL       NULL
     4         feature 4        NULL            -              NULL       NULL      
     5         feature 5        NULL            -              NULL       NULL

列出了所有功能，只有用户 4 投票的功能才会填充其他连接表，其他的只是 NULL。如果其他人投票支持功能 2，它仍然是 NULL，因为它与用户 4 无关，因为在此示例中用户 4 已登录。

问题是:

http://sqlfiddle.com/#!2/c3d10/3

http://sqlfiddle.com/#!2/c3d10/4

http://sqlfiddle.com/#!2/c3d10/5

http://sqlfiddle.com/#!2/c3d10/6

http://sqlfiddle.com/#!2/c3d10/7

SQLFiddle 中的所有上述查询都应该输出所有 5 个功能，而不管用户 ID。因此，必须以某种方式修改查询以显示所有功能 - 即使其他人为功能投票或没有投票或当前用户为功能投票也是如此。

Answer 1

应该这样做:

SELECT tmp1.id, name, votecnt, user_id, user_name FROM 
(SELECT *,(SELECT COUNT(*) FROM votes WHERE votes.featureid=features.id) AS "votecnt" FROM features) as tmp1
LEFT JOIN (SELECT features.id as feature_id, users.id as user_id, users.name as user_name FROM features
JOIN votes ON votes.featureid=features.id
JOIN users ON users.id=votes.userid
WHERE users.id=3) as tmp2 on tmp1.id = tmp2.feature_id

可能这不是最漂亮的sql，而且很有可能还有优化的空间