MySql 加入一对多关系 - 对于日期范围内的特定用户 ID

Question

我正在尝试起草一个 MySQL 查询来计算特定用户 (userid = 41) 在日期范围(2017-03-01 到 2017-04-01)内状态代码的出现次数。

这是我尝试使用的查询，但没有取得太大进展:

SELECT
  u.status,
  COUNT(*) AS countStatus   
FROM (SELECT programid, userid FROM portal_program_evalsusers WHERE userid=41) c
LEFT JOIN (
   SELECT
    programid,
    status
    FROM portal_program_eval 
    WHERE (portal_program_eval.date >= '2017-03-01' AND portal_program_eval.date <= '2017-04-01')
    GROUP BY status
) u ON u.programid = c.programid
  GROUP BY u.status

以下是有关表格的信息:

Tbl portal_program_eval
--------------------------
programid (primary key)
status
date

Tbl portal_program_evalsusers [relational table]
--------------------------
programid 
userid

我的查询做错了什么？

所需的输出是每个状态和计数:

 --------------------------
 status  | countStatus   
      1  |        6
      2  |       23
      3  |       14

Answer 1

经过一些额外的尝试和错误，我相信我找到了问题的答案:

SELECT
  u.status,
  COUNT(*) AS countStatus   
FROM (SELECT programid, userid FROM portal_program_evalsusers WHERE userid=41 GROUP BY programid) c
LEFT JOIN (
   SELECT
    programid,
    status,
    date
    FROM portal_program_eval 
    GROUP BY programid
) u ON c.programid = u.programid
  WHERE (u.date >= 2017-03-01 AND u.date < 2017-04-01)
  GROUP BY u.status