php - 过滤group_concat的结果，内部联接查询

新来的。下面的代码很好地返回了我想要的结果。唯一的问题是我不知道如何只返回一个特定的日期范围（在checkdate列中）。我能让它按checkdate的日期过滤的唯一方法是按empchecks.checkdate分组并使用havingcheckdate>=whatever。但我必须按employees.enum分组。我肯定我遗漏了一些简单的东西。

    SELECT employee_data.a,
           employees_data.b, employee_data.c,
           GROUP_CONCAT(empchecks.d
               ORDER BY empchecks.checkdate
              SEPARATOR '<br /> ')
           AS checknum,
           GROUP_CONCAT(empchecks.checkdate
               ORDER BY empchecks.checkdate
              SEPARATOR '<br /> ')
           AS checkdate,
      FROM employees
INNER JOIN empchecks ON employees.enum = empchecks.enum
  GROUP BY empchecks.enum

最佳答案

您需要添加WHERE子句。还有一个小的更正，AS checkdate之后的最后一个逗号不应该出现。

SELECT employee_data.a, employees_data.b, employee_data.c,
       GROUP_CONCAT(empchecks.d ORDER BY empchecks.checkdate SEPARATOR '<br /> ') as checknum,
       GROUP_CONCAT(empchecks.checkdate ORDER BY empchecks.checkdate SEPARATOR '<br /> ') as checkdate
    FROM employees
        INNER JOIN empchecks
            ON employees.enum = empchecks.enum
    WHERE empchecks.checkdate >= @begin_date
      AND empchecks.checkdate <= @end_date
    GROUP BY empchecks.enum;