-- 教练信息表 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 Enrollment.ClassID, Enrollment.EnrollmentDate AS StartDate, MAX(Lesson.LessonDate) AS EndDate FROM Enrollment JOIN Lesson ON Enrollment.ClassID = Lesson.ClassID GROUPBY Enrollment.ClassID
SELECT T1.StudentID, T1.ClassID, DATEDIFF(EndDate, Enrollment.EnrollmentDate) AS CompletionDuration FROM T1 JOIN Enrollment ON T1.StudentID=Enrollment.StudentID and T1.ClassID=Enrollment.ClassID WHERE CompletionStatus="已完成"
WITH T1 AS ( -- 子任务1:计算每个学员在其班级学习的结束日期。结果记为T1。 SELECT Lesson.StudentID, Lesson.ClassID, MAX(Lesson.LessonDate) AS EndDate FROM Lesson GROUPBY Lesson.ClassID,Lesson.StudentID ),T2 AS( -- 子任务2:在T1的基础上,连接报名表,计算完成状态为“已完成”的学员在其班级学习的完成时长。结果记为T2。 SELECT T1.StudentID, T1.ClassID, DATEDIFF(EndDate, EnrollmentDate) AS CompletionDuration FROM T1 JOIN Enrollment ON T1.StudentID=Enrollment.StudentID and T1.ClassID=Enrollment.ClassID WHERE CompletionStatus="已完成" ),T3 AS( -- 子任务3:在T2的基础上,连接班级表`Class`,按照课程ID分组,计算每个课程的平均完成时长。T3定义了查询结果的行范围。 SELECT Class.CourseID, AVG(T2.CompletionDuration) AS AverageCompletionDuration FROM T2 JOIN Class ON T2.ClassID = Class.ClassID GROUPBY Class.CourseID ) -- 子任务4:以T3左连接课程信息表,提取属性字段“课程名称”。所得即完整的查询结果。 SELECT T3.CourseID, Course.CourseName, AverageCompletionDuration FROM T3 JOIN Course ON T3.CourseID = Course.CourseID