时间戳

首页 > 解决方案 > GROUP BY 从历史表中检索最后一个事件

mysql - GROUP BY 从历史表中检索最后一个事件

问题描述

我有 3 个表，我想从历史表中检索最后一个事件，所以我写了这个请求，但结果并不好。还是我弄错了？谢谢你

    CREATE TABLE `equipment` (
      `id_equipment` int(10) UNSIGNED NOT NULL,
      `reference` varchar(32) DEFAULT NULL COMMENT 'clé AIRO2',
      `buying_date` date DEFAULT NULL,
      `first_use` date DEFAULT NULL,
      `checking_delai` varchar(45) DEFAULT NULL,
      `serial_number` varchar(256) DEFAULT NULL,
      `lot_number` varchar(256) DEFAULT NULL,
      `lapsing_date` date NOT NULL,
      `status` varchar(32) NOT NULL,
      `inspection` date NOT NULL,
      `compteur` int(11) DEFAULT NULL,
      `id_model` int(10) UNSIGNED DEFAULT NULL,
      `updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `detruit` int(1) DEFAULT NULL,
      `date_REEPREUVE` date DEFAULT NULL,
      `validite_apragaz` date DEFAULT NULL,
      `restituee_AJR` int(11) NOT NULL,
      `perdu` int(11) DEFAULT NULL,
      `maintenance_catt_o2` date DEFAULT NULL,
      `vendu` int(11) DEFAULT NULL,
      `colonne_inogen` date DEFAULT NULL COMMENT 'date changement colonne inogen',
      `rappel_525KS_dec19` int(1) DEFAULT NULL COMMENT 'rappel_cable_DEVILBISS_525_KS'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='matériel';

    CREATE TABLE `equipment_history` (
      `id_equipment_history` int(10) UNSIGNED NOT NULL,
      `id_user` int(10) UNSIGNED NOT NULL,
      `id_equipment` int(10) UNSIGNED DEFAULT NULL,
      `id_patient` int(11) DEFAULT NULL,
      `status` varchar(45) DEFAULT NULL,
      `status_date` datetime DEFAULT NULL,
      `updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `tuyau` tinyint(1) DEFAULT NULL,
      `cable_alim` tinyint(1) DEFAULT NULL,
      `compteur` int(11) DEFAULT NULL,
      `verif_alarme` tinyint(1) DEFAULT NULL,
      `verif_volume` tinyint(1) DEFAULT NULL,
      `verif_pression` tinyint(1) DEFAULT NULL,
      `verif_o2` tinyint(1) DEFAULT NULL,
      `verif_debit` tinyint(1) DEFAULT NULL,
      `remplacer_consommable` tinyint(1) DEFAULT NULL,
      `complet` tinyint(1) DEFAULT NULL,
      `comment_panne` text,
      `ras` tinyint(1) DEFAULT NULL,
      `panne` tinyint(1) DEFAULT NULL,
      `id_piece_jointe` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='historique de l''utilisation du matériel';

    CREATE TABLE `patient_has_equipment` (
      `id_patient_has_equipment` int(10) UNSIGNED NOT NULL,
      `id_intervention` int(10) UNSIGNED DEFAULT NULL,
      `id_equipment` int(10) UNSIGNED NOT NULL,
      `id_patient` int(10) UNSIGNED DEFAULT NULL,
      `date_attribution` date DEFAULT NULL,
      `id_user` int(11) DEFAULT NULL,
      `version_appli` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

要求 ：

SELECT
    equipment.id_equipment,
    equipment.reference,
    history.updated,
    history.`status`
FROM
    equipment
LEFT JOIN
    (
    SELECT
        *
    FROM
        `equipment_history`
    GROUP BY
        id_equipment
    ORDER BY
        `equipment_history`.`updated`
    DESC
) AS history
ON
    history.id_equipment = equipment.id_equipment
WHERE
    history.`status` = 3 AND equipment.id_equipment NOT IN(
    SELECT
        id_equipment
    FROM
        patient_has_equipment
)

谢谢

标签： mysqldatejoingroup-bygreatest-n-per-group

解决方案

group by子查询是问题所在。虽然这是大多数数据库中的语法错误，但 MySQL - 不幸的是 - 容忍这种语法（当ONLY_FULL_GROUP_BY禁用 sql 模式时），但不会按照您的想法做：您实际上是每个表中的任意记录equipment_id（该order by子句对这种行为）。

您需要过滤最后的历史记录而不是聚合。我认为这应该做你想要的：

select  
    e.id_equipment,
    e.reference,
    eh.updated,
    eh.status
from equipment e
inner join equipment_history eh on eh.id_equipment = e.id_equipment
where 
    eh.status = 3
    and eh.updated = (
        select max(eh1.updated) 
        from equipment_history eh1 
        where eh1.id_equipment = e.id_equipment
    )
    and not exists (
        select 1 
        from patient_has_equipment phe
        where phe.id_equipment = e.id_equipment
    )

推荐阅读