mysql - 为什么当用户没有提交时 MySQL 查询返回 null？

Question

我有以下表格:

提交内容:

+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| 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(36)                                                                                                                                                                                                                                                                                  | YES  |     | NULL    |                |
| removed     | tinyint(1) unsigned                                                                                                                                                                                                                                                                          | NO   |     | 0       |                |
| keywords    | varchar(255)                                                                                                                                                                                                                                                                                 | NO   |     | NULL    |                |
| ip          | int(10) unsigned                                                                                                                                                                                                                                                                             | NO   |     | NULL    |                |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+

用户:

+----------------+------------------------+------+-----+-------------------+-----------------------------+
| Field          | Type                   | Null | Key | Default           | Extra                       |
+----------------+------------------------+------+-----+-------------------+-----------------------------+
| id             | int(10) unsigned       | NO   | PRI | NULL              | auto_increment              |
| email          | varchar(128)           | NO   | MUL | NULL              |                             |
| hash           | varchar(64)            | NO   |     | NULL              |                             |
| salt           | varchar(32)            | NO   |     | NULL              |                             |
| username       | varchar(23)            | NO   |     | NULL              |                             |
| name           | varchar(32)            | NO   |     | NULL              |                             |
| about          | varchar(255)           | NO   |     | NULL              |                             |
| created        | datetime               | NO   |     | NULL              |                             |
| last_login     | timestamp              | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| created_ip     | int(10) unsigned       | NO   |     | NULL              |                             |
| last_login_ip  | int(10) unsigned       | NO   |     | NULL              |                             |
| remember_me    | tinyint(3) unsigned    | NO   |     | 0                 |                             |
| photo          | varchar(36)            | NO   |     | NULL              |                             |
| confirmed      | tinyint(1) unsigned    | NO   |     | 0                 |                             |
| confirm_code   | varchar(64)            | NO   |     | NULL              |                             |
| public_profile | tinyint(1)             | NO   |     | 1                 |                             |
+----------------+------------------------+------+-----+-------------------+-----------------------------+

submissions_comments:

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id       | int(10) unsigned | NO   | MUL | NULL    |                |
| submission_id | int(11)          | NO   | MUL | NULL    |                |
| comment       | text             | NO   |     | NULL    |                |
| parent_id     | int(10) unsigned | YES  | MUL | NULL    |                |
| created       | datetime         | NO   | MUL | NULL    |                |
| created_ip    | int(11)          | NO   |     | NULL    |                |
| helpful_count | int(11)          | NO   | MUL | NULL    |                |
| deleted       | tinyint(4)       | NO   | MUL | 0       |                |
+---------------+------------------+------+-----+---------+----------------+

最后，我的查询返回提交的计数(提示和请求)、评论以及有关用户的所有信息:

SELECT a.*, sc.user_id,
COALESCE(SUM(CASE WHEN b.type = "tip" THEN 1 ELSE 0 END),0) AS "tipsCount",
COALESCE(SUM(CASE WHEN b.type = "request" THEN 1 ELSE 0 END),0) AS "requestsCount", COALESCE(COUNT(DISTINCT sc.id),0) as "commentsCount"
FROM users as a
LEFT JOIN submissions as b ON a.id = b.user_id
LEFT JOIN submissions_comments as sc
ON a.id = sc.user_id WHERE a.username = ?
AND b.removed = 0
GROUP BY a.id, sc.user_id
LIMIT 1;

如果用户提交了提示或请求，则此查询有效，否则返回 NULL。我是否做错了分组依据或其他什么？

编辑:

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

Answer 1

我仍然不确定您需要什么，所以我给您这个查询，它返回特定用户的提示、请求和评论:

SELECT 
  users.id,
  SUM(CASE WHEN submissions.type = "tip" then 1 else 0 END) as tips,
  SUM(CASE WHEN submissions.type = "request" then 1 else 0 END) as requests,
  (select count(*) 
   from submissions_comments 
   where submissions_comments.user_id = users.id) as comments
FROM 
  users
LEFT JOIN 
  submissions ON submissions.user_id = users.id 
             AND submissions.removed = 0
WHERE
  users.username = 'amc123' 
group by
  users.id

SQLFiddle示例