-- 教练信息表 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 StudentID, TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) AS Age, CASE WHEN TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) <18THEN'少年' WHEN TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) BETWEEN18AND35THEN'青年' WHEN TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) BETWEEN36AND60THEN'中年' ELSE'老年' ENDAS AgeGroup FROM Student
SELECT AgeGroup, CourseID, Gender, COUNT(StudentID) AS MarketSize FROM T3 WHERE RegistrationDate <= DATE_SUB(CURDATE(), INTERVAL1YEAR) GROUPBY AgeGroup, CourseID, Gender
WITH T1 AS ( -- 子任务1:计算学员的年龄段。结果记为T1。 SELECT StudentID, TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) AS Age, CASE WHEN TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) <18THEN'少年' WHEN TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) BETWEEN18AND35THEN'青年' WHEN TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) BETWEEN36AND60THEN'中年' ELSE'老年' ENDAS AgeGroup FROM Student ), T2 AS ( -- 子任务2:计算学员的报名课程类型。结果记为T2。 SELECT Enrollment.StudentID, Class.CourseID FROM Enrollment JOIN Class ON Enrollment.ClassID = Class.ClassID ), T3 AS ( -- 子任务3:连接T1和T2,提取学员性别和学员报名时间,进行市场细分。结果记为T3。 SELECT T1.StudentID, T1.AgeGroup, T2.CourseID, Student.Gender, Student.RegistrationDate FROM T1 JOIN T2 ON T1.StudentID = T2.StudentID JOIN Student ON T1.StudentID = Student.StudentID ), T4 AS ( -- 子任务4:计算每个细分市场的客户数。结果记为T4。T4定义了查询结果的行范围。 SELECT AgeGroup, CourseID, Gender, COUNT(*) AS MarketSize FROM T3 GROUPBY AgeGroup, CourseID, Gender ), T5 AS ( -- 子任务5:计算一年前每个细分市场的客户数。结果记为T5。 SELECT AgeGroup, CourseID, Gender, COUNT(StudentID) AS MarketSize FROM T3 WHERE RegistrationDate <= DATE_SUB(CURDATE(), INTERVAL1YEAR) GROUPBY AgeGroup, CourseID, Gender ) -- 子任务6:T4左连接T5,处理null值,评估每个细分市场的当前的市场规模相比前一年市场规模的增长率,左连接`Course`提取“课程类型名称”。结果记为T6。所得即完整的查询结果。 SELECT T4.AgeGroup, Course.CourseName, T4.Gender, T4.MarketSize, (T4.MarketSize - IFNULL(T5.MarketSize, 0)) / IFNULL(T5.MarketSize, 1) AS GrowthPotential FROM T4 LEFTJOIN T5 ON T4.AgeGroup = T5.AgeGroup AND T4.CourseID = T5.CourseID AND T4.Gender = T5.Gender LEFTJOIN Course ON T4.CourseID=Course.CourseID