我有三个表:attendance、cv_target 和 candidate。我需要找到特定用户的候选人数。

我不是 MySQL 专家。我尝试了下面的查询，但找不到确切的值。

SELECT
   attendance_date,
   cv_target_date_for,
   cv_requirement,
   job_id,
   cv_target,
   achi,
   recruiter_comment,
   recruiter_rating
FROM
   attendance f
RIGHT JOIN
   (
      SELECT
         cv_requirement,
         cv_target,
         cv_target_date_for,
         achi,
         recruiter_comment,
         recruiter_rating
      FROM
         cv_target a
      LEFT JOIN
         (
            SELECT
               COUNT(candidate_id) AS achi,
               cv_target_date,
               fk_job_id
            FROM
               candidate
            GROUP BY
               fk_job_id,
               cv_target_date
         ) b
            ON a.cv_requirement = b.fk_job_id
            AND a.cv_target_date_for = b.cv_target_date
      WHERE
         cv_target_date_for BETWEEN '2014-02-01' AND '2014-03-01'
         AND cv_recruiter = '36'
   ) c
      ON f.attendance_date=c.cv_target_date_for
GROUP BY
   cv_requirement,
   cv_target_date_for
ORDER BY
   c`.`cv_target_date_for` ASC

出勤率

id   fk_user_id    attendance_date 
1    44            2014-02-24
2    44            2014-02-25
3    44            2014-02-26
4    44            2014-02-27
5    36            2014-02-24
6    44            2014-02-28

简历目标

id    cv_recruiter    cv_requirement    cv_target    cv_target_date_for
1     44              1                 3            2014-02-24
2     44              2                 2            2014-02-24
3     44              3                 2            2014-02-25
4     44              4                 3            2014-02-25
4     44              4                 3            2014-02-26

候选人

candidate_id    fk_posted_user_id    fk_job_id    cv_target_date
1               44                   1            2014-02-24
2               44                   3            2014-02-25
3               44                   3            2014-02-25
3               44                   4            2014-02-25
4               44                   4            2014-02-26
5               44                   5            2014-02-28
5               44                   5            2014-02-28

想要的结果

attendance_date    cv_target_date_for    job_id    cv_target    achi(count)
2014-02-24         2014-02-24            1         3            1
2014-02-24         2014-02-24            2         2            null
2014-02-25         2014-02-25            3         2            2
2014-02-25         2014-02-25            4         3            1
2014-02-26         2014-02-26            4         3            1
2014-02-27         2014-02-27            null      null         null
2014-02-28         null                  5         null         2

我得到的输出

attendance_date    cv_target_date_for    job_id    cv_target    achi(count)
2014-02-24         2014-02-24            1         3            1
2014-02-24         2014-02-24            2         2            null
2014-02-25         2014-02-25            3         2            2
2014-02-25         2014-02-25            4         3            1
2014-02-26         2014-02-26            4         3            1

日期 27 和 28 未显示。我也想要这些值。

最佳答案

原始答案

我想我明白你想要什么。以下假设您希望特定用户的特定范围内的所有出勤日期。对于这些出勤日期中的每一个，您都需要所有 cv_target 记录(如果有的话)。对于其中的每一个，您都需要计算候选人的数量。

使用子查询获取计数。这是唯一需要进入子查询的部分。仅在子查询中使用 GROUP BY 表达式，不 外部查询。仅选择您需要的字段。

使用 LEFT JOIN 从表达式左侧的表中获取所有记录，并仅从右侧的表中获取匹配的记录。因此，所有来自 attendance 的记录(匹配 WHERE 表达式)，以及来自 cv_target 的匹配记录(无论它们是否在候选子查询中匹配)，然后匹配来自候选子查询的记录。

试试这个:

SELECT
   DATE_FORMAT(a.attendance_date, '%Y-%m-%d') AS attendance_date,
   DATE_FORMAT(t.cv_target_date_for, '%Y-%m-%d') AS cv_target_date_for,
   t.cv_requirement AS job_id,
   t.cv_target,
   c.achi AS `achi(count)`
FROM
   attendance AS a
LEFT JOIN
   cv_target AS t
      ON a.fk_user_id = t.cv_recruiter
      AND a.attendance_date = t.cv_target_date_for
LEFT JOIN
   (
      SELECT
         COUNT(candidate_id) AS achi,
         fk_job_id,
         cv_target_date
      FROM
         candidate
      WHERE
         fk_posted_user_id = 44
         AND cv_target_date BETWEEN '2014-02-01' AND '2014-03-01'
      GROUP BY
         fk_job_id,
         cv_target_date
   ) AS c
      ON t.cv_requirement = c.fk_job_id
      AND t.cv_target_date_for = c.cv_target_date
WHERE
   a.fk_user_id = 44
   AND a.attendance_date BETWEEN '2014-02-01' AND '2014-03-01'
ORDER BY
   ISNULL(t.cv_target_date_for), t.cv_target_date_for, t.cv_requirement

请注意，正确的结果不需要以下行。但是，根据数据库结构和数据量，它可能会提高性能。

AND cv_target_date BETWEEN '2014-02-01' AND '2014-03-01'

ISNULL函数用于将 NULL 排序到底部。

我创建了一个 SQL Fiddle显示您请求的输出，cv_target_date_for 除外。无法输出数据中不存在的值。

更新

有了新数据和检索数据的新要求，其中 either cv_target or candidate 有一个数据特定的出勤日期，您需要添加另一个表来获取工作 ID。在您最初的问题中，您有一个包含 ID 号和职位的表格，但没有日期。

您可能需要重新考虑您的数据库设计。我不确定我是否理解您的表之间的关系，但是候选表的那两条新记录似乎是孤立的。您的所有联接都基于日期，但您似乎没有将工作 ID 号链接到日期的表。

您可以通过执行 cv_target 和 candidate 的 UNION 创建派生表。然后使用派生表作为联接的左侧。

更新查询:

SELECT
   DATE_FORMAT(a.attendance_date, '%Y-%m-%d') AS attendance_date,
   DATE_FORMAT(t.cv_target_date_for, '%Y-%m-%d') AS cv_target_date_for,
   j.job_id,
   t.cv_target,
   c.achi AS `achi(count)`
FROM
   attendance AS a
LEFT JOIN
   (
      SELECT
         cv_requirement AS job_id,
         cv_target_date_for AS job_date
      FROM
         cv_target
      WHERE 
         cv_recruiter = 44
         AND cv_target_date_for BETWEEN '2014-02-01' AND '2014-03-01'
      UNION
      SELECT
         fk_job_id AS job_id,
         cv_target_date AS job_date
      FROM
         candidate
      WHERE
         fk_posted_user_id = 44
         AND cv_target_date BETWEEN '2014-02-01' AND '2014-03-01'
   ) AS j
      ON a.attendance_date = j.job_date
LEFT JOIN
   cv_target AS t
      ON a.fk_user_id = t.cv_recruiter
      AND j.job_id = t.cv_requirement
      AND j.job_date = t.cv_target_date_for
LEFT JOIN
   (
      SELECT
         COUNT(candidate_id) AS achi,
         fk_job_id,
         cv_target_date
      FROM
         candidate
      WHERE
         fk_posted_user_id = 44
         AND cv_target_date BETWEEN '2014-02-01' AND '2014-03-01'
      GROUP BY
         fk_job_id,
         cv_target_date
   ) AS c
      ON j.job_id = c.fk_job_id
      AND j.job_date = c.cv_target_date
WHERE
   a.fk_user_id = 44
   AND a.attendance_date BETWEEN '2014-02-01' AND '2014-03-01'
ORDER BY
   ISNULL(t.cv_target_date_for), t.cv_target_date_for, j.job_id

我创建了一个更新的 SQL Fiddle显示您请求的输出，cv_target_date_for 除外。无法输出数据中不存在的值(即 2014-02-27)。

如果这是一个打字错误，您的意思是 2014-02-28，那么您需要从派生表而不是 cv_target 表中选择日期。您可能应该更改结果中的列标题，因为它不再是 cv_target_date_for 日期。

要从 cv_target 或 candidate 获取日期，请更改此行:

DATE_FORMAT(t.cv_target_date_for, '%Y-%m-%d') AS cv_target_date_for,

为此:

DATE_FORMAT(j.job_date, '%Y-%m-%d') AS job_date,

您可能需要调整表达式的顺序以满足您的需要。

关于来自多个表的 MySQL 结果计数得到错误的结果？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/22110460/