MySQL 一对多表结构，查询一的同时统计多的数量

最新推荐文章于 2024-07-25 11:09:27 发布

飘摇树

最新推荐文章于 2024-07-25 11:09:27 发布

阅读量2.1k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_37361514/article/details/125852462

版权

mysql

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

t_equipment_expand 与 t_equipment_insp是一对多的结构，通过equ_id关联

表结构如下：

CREATE TABLE `t_equipment_expand` (
`equ_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`danger_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '危险源类型',
`security_level` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '设备风险等级',
`area_code` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '所在地区编码',
`cant_town` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '行政区划代码',
`cant_street` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '设备所属村(街道)',
`equ_use` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '设备用途',
`equ_org_place` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`use_date` date DEFAULT NULL COMMENT '投用日期',
`equ_longitude` decimal(30,20) DEFAULT NULL COMMENT '经度',
`equ_latitude` decimal(30,20) DEFAULT NULL COMMENT '纬度',
`equ_elevation` decimal(20,4) DEFAULT '0.0000' COMMENT '海拔高度',
`use_status` int DEFAULT '0' COMMENT '使用状态',
`is_open` int DEFAULT '0' COMMENT '是否露天作业',
`work_place` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '工作场所',
`qr_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '二维码地址',
`cert_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '使用证证书编号',
`design_use_year` int DEFAULT '0' COMMENT '设计使用年限',
`car_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '车牌号',
`car_vin_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '车牌VIN码',
`insp_duty_status` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '监察业务状态',
`equ_org_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '场内编号',
`product_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '出厂编号',
`latest_insp_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '对应设备检验信息表检验日期最新的数据的记录id',
`equ_detail_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '设备详细地址',
`is_import_place` smallint DEFAULT '0' COMMENT '是否位于重要场所',
`use_status_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '使用状态名称',
`insp_duty_status_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '监察业务状态名称',
`prepare_insp_date` date DEFAULT NULL COMMENT '约请检验日期',
`next_insp_date` date DEFAULT NULL COMMENT '下次检验日期',
`insp_result` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '检验结论',
`insp_date` date DEFAULT NULL,
`rec_organ_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`rec_organ_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`sa_dept` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '安全管理部门',
`sa_person` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '安全管理人员',
`sa_tel` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '安全管理人员电话',
`equ_layout_num` int DEFAULT '0' COMMENT '设备布置数量',
`insp_equ_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '检验装置编号',
`equ_construct_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '制造编号',
`reg_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '设备注册代码',
`is_avail` int DEFAULT NULL COMMENT '是否可用',
`equ_inform` varchar(4000) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '设备公示内容',
`link_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '链接名称',
`link_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '链接地址',
PRIMARY KEY (`equ_id`) USING BTREE,
KEY `t_equipment_expand_cant_town` (`cant_town`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `t_equipment_insp` (
`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
`equ_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`insp_unisc_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '检验单位统一社会信用代码',
`insp_org_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '检验单位机构代码',
`insp_org_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '检验单位ID',
`insp_org_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '检验单位名称',
`test_person_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '检验员名称',
`insp_category` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '检验类别',
`next_insp_date` date DEFAULT NULL COMMENT '下次检验日期',
`insp_problem` varchar(4000) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`insp_result` varchar(4000) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '检验结论',
`insp_report_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '检验报告书编号',
`next_incheck_date` date DEFAULT NULL COMMENT '下次内检日期',
`next_outcheck_date` date DEFAULT NULL COMMENT '下次外检日期',
`next_pressure_date` date DEFAULT NULL COMMENT '下次耐压检验日期',
`insp_date` date DEFAULT NULL COMMENT '检验日期',
`insp_report_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '检验报告名称',
`insp_report_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '检验报告地址'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

具体SQL如下：

SELECT
	t.equ_id,
	count( t1.equ_id ) AS itemNum 
FROM
	t_equipment_expand t
	LEFT JOIN t_equipment_insp t1 ON t.equ_id = t1.equ_id
WHERE
	 t1.insp_date >= '2022-01-01' 
	AND t1.insp_date <= '2022-07-14' 
GROUP BY
	t.equ_id

GROUP BY t.equ_id 是必须的，不然永远只会有一条数据