mysql - 如果用户投票，除了计数之外还获取总计数

Question

我加入了三张 table 。 submissions、submissions_votes 和 users。

我想知道总共有多少有用的投票(这是所有 submissions_votes 的总和)，我知道了。

如果 sv.user_id 的 user_id 与正在查看的提交相关，我还想返回 0 或 1 的计数( bool 值)。 user_id 被传递到 WHERE 子句。

    SELECT s.*,
      u.username,
      u.photo as userPhoto,
      COALESCE(SUM(sv.up), 0) helpfulVotes
    FROM
      submissions s
    LEFT JOIN submissions_votes sv on s.id = sv.submission_id WHERE u.id = ?
    INNER JOIN users u
    ON s.user_id = u.id

我知道我需要一个额外的连接(在 sv.user_id = u.id 上)但是我会选择什么？那我会按 sv.id 分组吗？

编辑:

用户表:

+----------------+------------------------+------+-----+-------------------+-----------------------------+
| Field          | Type                   | Null | Key | Default           | Extra                       |
+----------------+------------------------+------+-----+-------------------+-----------------------------+
| id             | int(10) unsigned       | NO   | PRI | NULL              | auto_increment              |
| email          | varchar(128)           | NO   | MUL | NULL              |                             |
| username       | varchar(23)            | NO   |     | NULL              |                             |
| type           | enum('normal','admin') | NO   |     | normal            |                             |
| about          | varchar(255)           | NO   |     | NULL              |                             |
| photo          | varchar(32)            | NO   |     | NULL              |                             |
+----------------+------------------------+------+-----+-------------------+-----------------------------+

submissions_votes 表:

+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| submission_id | int(10) unsigned    | NO   | MUL | NULL    |                |
| when          | datetime            | NO   |     | NULL    |                |
| user_id       | int(10) unsigned    | NO   | MUL | NULL    |                |
| up            | tinyint(3) unsigned | NO   |     | NULL    |                |
| down          | tinyint(3) unsigned | NO   |     | NULL    |                |
+---------------+---------------------+------+-----+---------+----------------+

提交表:

+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| Field       | Type                                                                                                                                                                                                                                                                                | Null | Key | Default | Extra          |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| id          | int(10) unsigned                                                                                                                                                                                                                                                                    | NO   | PRI | NULL    | auto_increment |
| title       | varchar(255)                                                                                                                                                                                                                                                                        | NO   | MUL | NULL    |                |
| slug        | varchar(255)                                                                                                                                                                                                                                                                        | NO   |     | NULL    |                |
| description | mediumtext                                                                                                                                                                                                                                                                          | NO   |     | NULL    |                |
| user_id     | int(11)                                                                                                                                                                                                                                                                             | NO   | MUL | NULL    |                |
| created     | datetime                                                                                                                                                                                                                                                                            | NO   |     | NULL    |                |
| type        | enum('tip','request')                                                                                                                                                                                                                                                               | NO   |     | NULL    |                |
| thumbnail   | varchar(64)                                                                                                                                                                                                                                                                         | YES  |     | NULL    |                |
| removed     | tinyint(1) unsigned                                                                                                                                                                                                                                                                 | NO   |     | 0       |                |
| keywords    | varchar(255)                                                                                                                                                                                                                                                                        | NO   |     | NULL    |                |
| ip          | int(10) unsigned                                                                                                                                                                                                                                                                    | NO   |     | NULL    |                |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+

Answer 1

您可以使用 CASE 和 SUM 来检查 sv.user_id = input user_id(分组每次提交)。如果SUM为1，那么输入的user_id有提交，否则没有。 因此，您输入的 user_id 将进入 CASE 函数。

此外，COALESCE(SUM(sv.up), 0) 需要根据 submissions 和 users 表中的任何列进行分组已选中。

下面是基于SQL Fiddle here中表的查询.

SELECT 
  s.id as submission_id,
  s.title as submission_title,
  MAX(u.email) as submission_user_email,
  COALESCE(SUM(sv.up), 0) helpfulVotes,
  SUM(CASE sv.user_id
      WHEN ? THEN 1
      ELSE 0
      END) User_Submission
FROM
  submissions s
LEFT JOIN submissions_votes sv on s.id = sv.submission_id
INNER JOIN USERS u
ON s.user_id = u.id
GROUP BY s.id, s.title;

(如果需要从 submissions 表中选择更多列，则需要对它们进行分组或聚合)