首页 / MYSQL / 来自多个表的MySQL结果计数得到错误的结果？

来自多个表的MySQL结果计数得到错误的结果？

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了来自多个表的MySQL结果计数得到错误的结果？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6403字，纯文字阅读大概需要10分钟。

内容图文

我有三个表：出勤率,cv_target和候选人.我需要找到特定用户的候选人数.

我不是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

cv_target

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从表达式左侧的表中获取所有记录,并仅匹配右侧表中的记录.所以来自出勤的所有记录(与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除外.无法输出数据中不存在的值.

UPDATE

使用新数据和检索数据的新要求,其中cv_target或候选者具有特定出勤日期的数据,您需要添加另一个表来获取作业ID.在您的原始问题中,您有一张带有身份证号码和职位的表格,但它没有日期.

您可能想重新考虑数据库设计.我不确定我是否了解您的表格如何相互关联,但候选表格的这两个新记录似乎是孤立的.您的所有联接都基于日期,但您似乎没有将作业ID号与日期相关联的表.

您可以通过执行cv_target和候选的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或候选者中获取日期,请更改此行：

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结果计数得到错误的结果？全部内容，希望文章能够帮你解决来自多个表的MySQL结果计数得到错误的结果？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/901934.html

来源：【匿名】