-- 教练信息表 CREATETABLE Instructor ( InstructorID INT COMMENT '教练ID;主键;自增', Name VARCHAR(100) COMMENT '姓名;例如:李四', Gender ENUM('男', '女', '其他') COMMENT '性别;值为:男/女/其他', Phone VARCHAR(20) COMMENT '联系电话;例如:139********', DrivingExperience INT COMMENT '驾龄;单位:年' ) COMMENT='教练信息表';
-- 班级类型表 CREATETABLE ClassType ( TypeID INT COMMENT '班级类型ID;主键;自增', TypeName VARCHAR(100) COMMENT '类型名称;例如:"周末班", "集中培训班"', Description TEXT COMMENT '描述' ) COMMENT='班级类型表';
-- 课程信息表 CREATETABLE Course ( CourseID INT COMMENT '课程ID;主键;自增', CourseName VARCHAR(100) COMMENT '课程名称;如:F本-轻便摩托车、C2-自动挡小汽车', TheoryHours INT COMMENT '理论课时数', PracticeHours INT COMMENT '实践课时数' ) COMMENT='课程信息表';
-- 班级信息表 CREATETABLE Class ( ClassID INT COMMENT '班级ID;主键;自增', CourseID INT COMMENT '课程ID;外键:课程ID', ExpireDate DATE COMMENT '过期日期;格式为YYYY-MM-DD', ClassTypeID INT COMMENT '班级类型ID;外键:班级类型ID' ) COMMENT='班级信息表';
-- 课节 CREATETABLE Lesson ( LessonID INT COMMENT '课节ID;主键;自增', StudentID INT COMMENT '学员ID;外键:学员ID', ClassID INT COMMENT '班级ID;外键:班级ID', InstructorID INT COMMENT '教练ID;外键:教练ID', TheoryHours INT COMMENT '理论课时数', PracticeHours INT COMMENT '实践课时数' LessonStatusCode VARCHAR(31) COMMENT '课节状态代码;未开始/已完成/学员未赴约/教练未赴约' VehicleID INT COMMENT '车辆ID;外键:车辆ID', LessonDate DATE COMMENT '课节日期', LessonTime TIME COMMENT '课节时间', Rating INT COMMENT '学员打分;1-5分,5分满分', Feedback VARCHAR(255) COMMENT '学员反馈' );
-- 学习报名记录表 CREATETABLE Enrollment ( EnrollmentID INT COMMENT '报名记录ID;主键;自增', StudentID INT COMMENT '学员ID;外键:学员ID', ClassID INT COMMENT '班级ID;外键:班级ID', EnrollmentDate DATE COMMENT '报名日期;格式为YYYY-MM-DD', CompletionStatus ENUM('未完成', '已完成', '进行中') COMMENT '完成状态;值为:未完成/已完成/进行中' ) COMMENT='学习报名记录表';
-- 车辆信息表 CREATETABLE Vehicle ( VehicleID INT COMMENT '车辆ID;主键;自增', LicensePlate VARCHAR(20) COMMENT '车牌号;例如:川A12345', Model VARCHAR(50) COMMENT '车型;例如:奥迪A4', PurchaseDate DATE COMMENT '购置日期;格式为YYYY-MM-DD', Status ENUM('使用中', '维修中', '闲置') COMMENT '状态;值为:使用中/维修中/闲置' ) COMMENT='车辆信息表';
-- 考试记录表 CREATETABLE Exam ( ExamID INT COMMENT '考试记录ID;主键;自增', StudentID INT COMMENT '学员ID;外键:学员ID', ClassID INT COMMENT '班级ID;外键:班级ID', ExamType ENUM('科目一', '科目二', '科目三', '科目四') COMMENT '考试类型;值为:科目一/科目二/科目三/科目四', ExamDate DATE COMMENT '考试日期;格式为YYYY-MM-DD', Score DECIMAL(5, 2) COMMENT '成绩;例如:85.50', Passed INT COMMENT '是否通过;1为通过,0为未通过' ) COMMENT='考试记录表';
-- 订单信息表 CREATETABLEOrder ( OrderID INT COMMENT '订单ID;主键;自增', StudentID INT COMMENT '学员ID;外键:学员ID', ClassID INT COMMENT '班级ID;外键:班级ID', OrderDate DATE COMMENT '下单日期;格式为YYYY-MM-DD', TotalAmount DECIMAL(10, 2) COMMENT '总金额;例如:2000.00', Status ENUM('待支付', '已支付', '已取消') COMMENT '状态;值为:待支付/已支付/已取消' ) COMMENT='订单信息表';
-- 支付记录表 CREATETABLE Payment ( PaymentID INT COMMENT '支付记录ID;主键;自增', OrderID INT COMMENT '订单ID;外键:订单ID', PaymentDate DATE COMMENT '支付日期;格式为YYYY-MM-DD', Amount DECIMAL(10, 2) COMMENT '支付金额;例如:2000.00', PaymentMethod ENUM('现金', '信用卡', '在线支付') COMMENT '支付方式;值为:现金/信用卡/在线支付' ) COMMENT='支付记录表';
SELECT L1.InstructorID, COUNT(DISTINCT L2.StudentID) AS repeat_students FROM Lesson AS L1 JOIN Lesson AS L2 ON L1.InstructorID = L2.InstructorID AND L1.StudentID = L2.StudentID AND L1.LessonID <> L2.LessonID GROUPBY L1.InstructorID
WITH T1 AS ( -- 子任务1:计算“每个教练所授课学员打分的均值”。结果记为T1。 SELECT InstructorID, AVG(Rating) AS average_rating FROM Lesson WHERE Rating ISNOTNULL GROUPBY InstructorID ), T2 AS ( -- 子任务2:计算“每个教练所授课的学员数”。结果记为T2。 SELECT InstructorID, COUNT(DISTINCT StudentID) AS total_students FROM Lesson GROUPBY InstructorID ), T3 AS ( -- 子任务3:计算“上过该教练课程的学员中再次选择该教练的学员数”。结果记为T3。 SELECT L1.InstructorID, COUNT(DISTINCT L1.StudentID) AS repeat_students FROM Lesson AS L1 JOIN Lesson AS L2 ON L1.StudentID = L2.StudentID AND L1.InstructorID = L2.InstructorID AND L1.LessonID <> L2.LessonID GROUPBY L1.InstructorID ), T4 AS ( -- 子任务4:计算“每个教练的上过该教练课程的学员再次选择该教练的比例”,结果记为T4。 SELECT T2.InstructorID, IFNULL(T3.repeat_students, 0) / T2.total_students AS repeat_ratio FROM T2 LEFTJOIN T3 ON T2.InstructorID = T3.InstructorID ),T5 AS ( -- 子任务5:对T1与T4的“教练ID”求并集。结果记为T5。T5定义了查询结果的数据范围。 SELECT InstructorID FROM T1 UNION SELECT InstructorID FROM T4 ) -- 子任务6:以T5左连接T1和T4,处理NULL值,并左连接教练信息表,提取剩余所需属性字段。所得即完整的查询结果。 SELECT T5.InstructorID,Instructor.Name,IFNULL(T1.average_rating,0) average_rating,T4.repeat_ratio FROM T5 LEFTJOIN T1 ON T1.InstructorID=T5.InstructorID LEFTJOIN T4 ON T4.InstructorID = T5.InstructorID LEFTJOIN Instructor ON Instructor.InstructorID = T5.InstructorID