主页 / user-240852

Ernest Elikem's questions

Martin Hope
Ernest Elikem
Asked: 2021-11-24 04:04:59 +0800 CST
  • 0

在下面的表格中，我有一些字段重复id的字段。我想获取每个对应 的字段的值。job_idjob_idMAXIMUMidunique job_id

我现在已经尝试了该GROUP_BY子句，但它没有给我正确的结果。

例如，

SELECT MAX(id) AS max_id, job_id
FROM job_status_update
GROUP BY job_id

会产生：

在此处输入图像描述

但是从第一张图片来看，job_status_update 表id中的6对应于job_status_id3而不是1 所以我想编写一个查询来获取这些字段 max_id：job_id

请问有人可以给我一个关于该怎么做的指针吗？谢谢！

在此处输入图像描述

mysql phpmyadmin
Martin Hope
Ernest Elikem
Asked: 2021-11-06 02:02:53 +0800 CST
  • 0

您好数据库专家。我的 MySQL 查询有一个小问题，其中一个字段可以返回NULL 如果它返回 NULL，即使该记录应该存在，整个记录也不会出现。

查询是：

SELECT 
        `job_status_update`.`job_status_id` AS `last_job_status_id`,
        `job_status`.`status_name` AS `last_status_name`,
        `job_report`.`id` AS `job_report_id`,
        `job_report`.`start_time`,
        `job_report`.`form_number`,
        `job_report`.`activity`,
        `job_report`.`end_time`,
        `job_report`.`date_added` AS `reported_date`,
        `job_report`.`omc_site_manager_signature`,
        `omc`.`name` AS `omc`,
        `assigned_job`.`date_added` AS `date_assigned`,
        `job`.`id` AS `job_id`,
        `job_category`.`category_name` AS `job_category_name`,
        `job`.`date_received`,
        `job`.`work_order_number`,
        `job`.`job_description`,
        `job`.`omc_site_name`, `job`.`deadline`,
        `equipment`.`name` AS `equipment_name`,
        `reported_faulty_equipment`.`fault`,
        `reported_faulty_equipment`.`root_cause`,
        `reported_faulty_equipment`.`solution`
FROM `job_report` 
        INNER JOIN `assigned_job` ON `job_report`.`assigned_job_id` = `assigned_job`.`assigned_job_id`
        INNER JOIN `job` ON `assigned_job`.`job_id` = `job`.`id`
        LEFT JOIN `job_status_update` ON `job_status_update`.`job_id` = `assigned_job`.`job_id`
        LEFT JOIN `job_status` ON `job_status_update`.`job_status_id` = `job_status`.`id`
        INNER JOIN `reported_faulty_equipment` ON `reported_faulty_equipment`.`job_report_id` = `job_report`.`id`
        INNER JOIN `equipment` ON `equipment`.`id` = `reported_faulty_equipment`.`equipment_id`
        INNER JOIN `job_category` ON `job`.`job_category_id` = `job_category`.`id`
        INNER JOIN `omc` ON `job`.`omc_id` = `omc`.`id`         
WHERE `job_report`.`id` = 6
        AND `job_status_update`.`id` = (SELECT MAX(`job_status_update`.`id`) AS `last_job_status_update` FROM `job_status_update`)

我意识到查询的违规部分在于 WHERE 子句。

AND `job_status_update`.`id` = (SELECT MAX(`job_status_update`.`id`) AS `last_job_status_update` FROM `job_status_update`)

因为job_status_update. id因此NULL，如果“作业”没有“作业状态更新”，则查询不会返回一行。

请问我如何返回记录，即使job_status_update。id一片空白？我已经看到了使用该IF...ELSE语句的示例。但我无法取得任何进展。

EXPLAIN输出：

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   PRIMARY     job_report  const   PRIMARY,assigned_job_id,fk_Job_Report_Job_Report_1  PRIMARY     4   const   1   
1   PRIMARY     assigned_job    const   PRIMARY,job_id,fk_Assigned_Jobs_Job_1   PRIMARY     4   const   1   
1   PRIMARY     job     const   PRIMARY,fk_Job_Job_Category_2,fk_Job_Omc_id     PRIMARY     4   const   1   
1   PRIMARY     job_status_update   const   PRIMARY,job_id  PRIMARY     4   const   1   
1   PRIMARY     job_status  const   PRIMARY     PRIMARY     4   const   1   
1   PRIMARY     job_category    const   PRIMARY     PRIMARY     4   const   1   
1   PRIMARY     omc     const   PRIMARY     PRIMARY     4   const   1   
1   PRIMARY     reported_faulty_equipment   ref     job_report_id,fk_Faulted_Equipment_Job_Report_1,eq...   job_report_id   4   const   2   
1   PRIMARY     equipment   eq_ref  PRIMARY     PRIMARY     4   resl_v2.reported_faulty_equipment.equipment_id  1   
2   SUBQUERY    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
mysql