mysql - 如果Mysql中不存在具有唯一id的记录，则返回0值记录

Question

我有两个表，verified_users 和 cfw_documents。我想显示每个用户每月上传的文档数量。

我尝试了以下查询-

SELECT  a.user_id,count(distinct d.db_filename), MONTH(d.doc_date) as upload_month
FROM verified_users a
join cfw_documents d on a.user_id=d.user_id and d.doc_date >= a.created_on
GROUP BY 1,3 ORDER BY 1;

我得到的输出为:

user_ id   count      month
1          15         3    
1          20         6    
2          5          1    
2          1          4

每个用户都缺少未上传记录的月份。我想用计数 0 显示这样的月份。

预期输出:

user_ id   count      month
1          15         3
1           0         4 
1           0         5 
1          20         6
1           0         7
1           0         8
1           0         9    
2           5         1
2           0         2
2           0         3 
2           1         4
2           0         5
2           0         6

我尝试搜索，但所有解决方案都只有计数和月份列，但没有表中的其他列。请建议解决此问题的方法。

Answer 1

您应该使用月份列表和左连接(或使用月份表)

  SELECT  a.user_id, ifnull(count(distinct d.db_filename), 0), t.month  as upload_month
  FROM (

    select  1  month from dual 
    union
    select  2  from dual 
    union
    select  3  from dual 
    union
    select  4  from dual 
    union
    select  5  from dual 
    union
    select  6  from dual 
    union
    select  7  from dual 
    union
    select  8  from dual 
    union
    select  9  from dual 
    union
    select  10  from dual 
    union
    select  11  from dual 
    union
    select  12  from dual 

  ) t 
  left join user_documents a on t.month = MONTH(d.doc_date)
  join  cfw_documents d on a.user_id=d.user_id and d.doc_date >= a.created_on
  GROUP BY 1,3 ORDER BY 1;

查看您的示例似乎您只需要 macthing 月份的结果，因此您应该使用内部联接

      SELECT  a.user_id, ifnull(count(distinct d.db_filename), 0), t.month  as upload_month
      FROM (

        select  1  month from dual 
        union
        select  2  from dual 
        union
        select  3  from dual 
        union
        select  4  from dual 
        union
        select  5  from dual 
        union
        select  6  from dual 
        union
        select  7  from dual 
        union
        select  8  from dual 
        union
        select  9  from dual 
        union
        select  10  from dual 
        union
        select  11  from dual 
        union
        select  12  from dual 

      ) t 
      INNER join user_documents a on t.month = MONTH(d.doc_date)
      INNER join  cfw_documents d on a.user_id=d.user_id and d.doc_date >= a.created_on
      GROUP BY 1,3 ORDER BY 1;