MySQL多表联查、分组查询每组最新的一条数据，并对某一字段进行条件统计

起因

本人在写查询的时候遇到了如题所述的要求，多次尝试无果（纯菜），最后发现“sql不会写？那就疯狂联表+堆子查询”。如果有更好的写法，也请在评论区赐教

表信息如下

equipment表：存储设备的名称等信息。其实这个表在接下来的查询中的作用，仅仅只是通过eq_id和其它表进行关联，然后查询到设备名称

DROP TABLE IF EXISTS `equipment`;
CREATE TABLE `equipment`  (
  `eq_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '设备ID',
  `eq_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '设备名称',
  `purchase_time` date NULL DEFAULT NULL COMMENT '采购时间',
  `service_life` int(11) NULL DEFAULT NULL COMMENT '预计使用寿命/年',
  `supplier_id` int(11) NULL DEFAULT NULL COMMENT '供应商ID',
  PRIMARY KEY (`eq_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

overhaul表：要求从这个表中查出最新的一条检修结果，并统计“检修结果”为“正常”(overhaul_reasult = 0)一共多少次

DROP TABLE IF EXISTS `overhaul`;
CREATE TABLE `overhaul` (
  `overhaul_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL COMMENT '员工ID',
  `overhaul_time` date NULL DEFAULT NULL COMMENT '检修时间',
  `overhaul_result` int(11) NULL DEFAULT NULL COMMENT '检修结果',
  `eq_id` int(11) NULL DEFAULT NULL '设备ID',
  PRIMARY KEY (`overhaul_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

risk_warning表

DROP TABLE IF EXISTS `risk_warning`;
CREATE TABLE `risk_warning` (
  `risk_warning_id` int(11) NOT NULL AUTO_INCREMENT,
  `eq_id` int(11) NULL DEFAULT NULL COMMENT '设备ID',
  `is_risk_warning` int(11) NULL DEFAULT NULL COMMENT '设备是否存在风险',
  PRIMARY KEY (`risk_warning_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

具体要求

• 从“overhaul”表中查出“每一个设备”的最新的一条检修结果，并统计“检修结果”为“正常”(overhaul_reasult = 0)的次数；
• 查询与"eq_id"对应的“eq_name”（设备名称）；
• 查询“risk_warning”的所有内容。
  然后这个需求对我这种菜🐔来说有些复杂了，所以我就干脆把这三个需求拆成三个子查询，最后拼在一起

查询语句及其结果

• 第一个子查询：从“overhaul”表中查出“每一个设备”的最新的一条检修结果，并统计“检修结果”为“正常”(overhaul_reasult = 0)的次数；并通过“eq_id”（设备ID）与“equipment”表进行内联，查出“eq_name”（设备名称）

SELECT equipment.eq_id, equipment.eq_name, COUNT(overhaul_result = 0 OR NULL) AS overhaul_count, MAX(overhaul_time) AS last_overhaul_time
FROM overhaul
	JOIN equipment ON equipment.eq_id = overhaul.eq_id
GROUP BY equipment.eq_id

查询结果

查出这个后，准备第二步——因为使用了MAX()方法，现在的结果已经是最新的时间了，所以再利用"last_overhaul_time"字段与“overhaul”表进行内联，查出最新的“overhaul_result”

• 第二个子查询：这时把第一个子查询写到JOIN后面，与“overhaul”表进行内联

SELECT a.*, overhaul.overhaul_result
FROM overhaul
JOIN
(SELECT equipment.eq_id, equipment.eq_name, COUNT(overhaul_result = 0 OR NULL) AS overhaul_count, MAX(overhaul_time) AS last_overhaul_time
FROM overhaul
	JOIN equipment ON equipment.eq_id = overhaul.eq_id
GROUP BY equipment.eq_id ) AS a ON overhaul.overhaul_time = a.last_overhaul_time

查询结果

• 第三个子查询：如法炮制，通过“eq_id”，将第二个子查询与“risk_warning”进行内联，最后查出我们想要的结果

SELECT a.*, risk_warning.risk_waring_id, risk_warning.is_risk_warning
FROM risk_warning
JOIN
(SELECT a.*, overhaul.overhaul_result
FROM overhaul
JOIN
(SELECT equipment.eq_id, equipment.eq_name, COUNT(overhaul_result = 0 OR NULL) AS overhaul_count, MAX(overhaul_time) AS last_overhaul_time
FROM overhaul
	JOIN equipment ON equipment.eq_id = overhaul.eq_id
GROUP BY equipment.eq_id ) AS a ON overhaul.overhaul_time = a.last_overhaul_time ) AS a ON risk_warning.eq_id = a.eq_id

查询结果

总结

所以以后如果不会写SQL，那就疯狂子查询+内联罢！