mysql 存储过程之复杂查询

有这样一个场景,一个plan可能对应多个place,多个时间,多条记录,一个用户
表1为:
CREATE TABLE IF NOT EXISTS InspectionPlan (
id char(36) NOT NULL COMMENT ‘巡检计划ID’,
name char(36) NOT NULL COMMENT ‘巡检计划名字’,
status smallint(6) NOT NULL COMMENT ‘巡检计划状态’,
executorUserId char(36) NOT NULL COMMENT ‘执行人员名字’,
times varchar(128) NOT NULL COMMENT ‘时间字符串,若有多个时间字符串用逗号间隔’,
weeks varchar(128) NOT NULL COMMENT ‘星期字符串,一周中占用哪几天,1表示周一,…7表示周日’,
creatAt datetime NOT NULL COMMENT ‘巡检计划创建时间’,
description varchar(128) DEFAULT NULL COMMENT ‘巡检计划描述’,
PRIMARY KEY (id),
KEY rf_user_inspectionPlan (executorUserId),
CONSTRAINT rf_user_inspectionPlan FOREIGN KEY (executorUserId) REFERENCES User (id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表2为:
CREATE TABLE IF NOT EXISTS InspectionPlanPlace (
id char(36) NOT NULL,
planId char(36) DEFAULT NULL COMMENT ‘巡检计划ID’,
placeId char(36) DEFAULT NULL COMMENT ‘巡检计划关联的巡检地方ID’,
PRIMARY KEY (id),
KEY rf_inspectionPlanPlace1 (planId),
KEY rf_inspectionPlanPlace2 (placeId),
CONSTRAINT rf_inspectionPlanPlace1 FOREIGN KEY (planId) REFERENCES InspectionPlan (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT rf_inspectionPlanPlace2 FOREIGN KEY (placeId) REFERENCES InspectionPlace (id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表3为:
CREATE TABLE IF NOT EXISTS InspectionRecord (
id char(36) NOT NULL COMMENT ‘巡检任务ID’,
planId char(36) DEFAULT NULL COMMENT ‘巡检任务相关联的巡检计划ID’,
creatTime datetime DEFAULT NULL COMMENT ‘开始时间’,
endTime datetime DEFAULT NULL COMMENT ‘结束时间’,
executeUserId char(36) DEFAULT NULL COMMENT ‘用户ID’,
status smallint(6) DEFAULT NULL COMMENT ‘巡检任务状态,1 表示未提交,2表示正常,3表示异常’,
photoUrl text COMMENT ‘巡检图片URL;若有多个用分号分割’,
description char(255) DEFAULT NULL COMMENT ‘巡检任务描述’,
PRIMARY KEY (id),
KEY rf_InspectionRecord1 (executeUserId),
KEY rf_InspectionRecord2 (planId),
CONSTRAINT rf_InspectionRecord1 FOREIGN KEY (executeUserId) REFERENCES User (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT rf_InspectionRecord2 FOREIGN KEY (planId) REFERENCES InspectionPlan (id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表4为:
CREATE TABLE IF NOT EXISTS User (
id char(36) NOT NULL,
name varchar(64) DEFAULT NULL,
password varchar(64) DEFAULT NULL,
displayName varchar(128) DEFAULT NULL,
isEnable tinyint(6) DEFAULT ‘1’,
createTime datetime DEFAULT NULL,
updateTime datetime DEFAULT NULL,
mobile varchar(20) NOT NULL,
email varchar(100) DEFAULT NULL,
openId char(36) DEFAULT NULL,
accelCtrlId char(36) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY index_openId (openId),
CONSTRAINT rf_User1 FOREIGN KEY (openId) REFERENCES WeixinUserInfo (openId) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表5为:
CREATE TABLE IF NOT EXISTS InspectionPlace (
id char(36) NOT NULL COMMENT ‘巡检地方ID’,
name char(64) DEFAULT NULL COMMENT ‘巡检地方名字’,
machineId char(36) DEFAULT NULL COMMENT ‘巡检地方关联的打卡ID’,
createTime datetime DEFAULT NULL COMMENT ‘创建时间’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

下面需要从这五个表中进行planId，开始时间和结束时间来查询这个plan的统计信息。如果分别从这五个表查询信息的话需要对数据库进行了多次访问,从而会降低读写效率。所以, 笔者想用存储过程,先分别从这五个表中抽出相关信息一个视图,再根据筛选条件创建存储过程从视图中查询需要信息。
基本如下：
创建视图1
select plan.id AS id,plan.name AS name,plan.status AS status,plan.executorUserId AS executorUserId,plan.times AS times,plan.weeks AS weeks,plan.creatAt AS creatAt,plan.description AS description,user.displayName AS executorUserName,pp.placeId AS placeId,place.name AS placeName from (((InspectionPlan plan left join InspectionPlanPlace pp on((plan.id = pp.planId))) left join User user on((user.id = plan.executorUserId))) left join InspectionPlace place on((place.id = pp.placeId)));

在视图1的基础上创建视图2:
select vwInspectionPlanWithPlaces.id AS id,vwInspectionPlanWithPlaces.name AS name,vwInspectionPlanWithPlaces.status AS status,vwInspectionPlanWithPlaces.executorUserId AS executorUserId,vwInspectionPlanWithPlaces.executorUserName AS executorUserName,vwInspectionPlanWithPlaces.times AS times,vwInspectionPlanWithPlaces.weeks AS weeks,vwInspectionPlanWithPlaces.creatAt AS creatAt,vwInspectionPlanWithPlaces.description AS description,group_concat(vwInspectionPlanWithPlaces.placeName separator ‘,’) AS placeName,group_concat(vwInspectionPlanWithPlaces.placeId separator ‘,’) AS placeId from vwInspectionPlanWithPlaces group by vwInspectionPlanWithPlaces.id

在视图2的基础上根据筛选条件创建存储过程1:
CREATE PROCEDURE uspQueryStatInspection(
in start_time DATETIME,
in end_time DATETIME,
in pId char(36)
)
BEGIN
select
pp.id as id,
pp.name as name,
pp.status as status,
pp.executorUserId as executorUserId,
pp.executorUserName as executorUserName,
pp.times as times,
pp.weeks as weeks,
pp.creatAt as creatAt,
pp.description as description,
pp.placeName as placeName,
pp.placeId as placeId,
b.missing as missing,
b.success as success,
b.fail as fail,
b.total as total
from (select
r.planId as planId,
count(r.status=1 or null) as missing,
count(r.status=2 or null) as success,
count(r.status=3 or null) as fail,
count(r.status or null) as total
from InspectionRecord r where r.creatTime > start_time and r.creatTime < end_time and r.planId=pid group by planId) b
left join vwInspectionPlanWithPlacesGroup pp on(pp.id=b.planId) ;
END

调用存储过程得到相对应的数据:
call uspQueryStatInspection(‘2017-03-23 17:51:28’,’2019-03-23 17:51:28’,’1’);