day13_超过5名学生的课

一、题目

一张courses课程表

有student 学生姓名、

class 学生所在的班级

两个字段

(student,class)是该表的主键

二、题目要求

查询至少有5个学生的所有班级

其实就是找出courses表中class数量大于等于5的班级

三、示例

从表里看到:

math班有6个同学(6条数据)

English班有1个同学

biology班有1个同学

computer班有1个同学

班级有大于等于5个学生的是math班

四、分析

1、先按班级进行分组,查出每个班级的学生数量

select t.class from courses t group by t.class

从上图的数据中可以

看出用count(student)>=5就能找出符合题意的班级

①count(*):统计表里面所有数据

②count(student):统计表里面student这一列非null的数据,用class列也可以

五、sql实现

select

t.class

from courses t

group by t.class

having count(student) >= 5

 

#include <iostream> #include <fstream> #include <cstring> #include <vector> #include <cctype> #include <algorithm> // 自定义内存分配器 class CourseAllocator { public: static void* allocate(size_t size) { return ::operator new(size); } static void deallocate(void* ptr, size_t size) { ::operator delete(ptr); } }; // 自定义向量类(替代std::vector) template <typename T> class LowLevelVector { private: T* data; size_t capacity; size_t size; public: LowLevelVector() : data(nullptr), capacity(0), size(0) {} ~LowLevelVector() { if (data) { for (size_t i = 0; i < size; ++i) { data[i].~T(); } CourseAllocator::deallocate(data, capacity * sizeof(T)); } } void push_back(const T& item) { if (size >= capacity) { size_t new_capacity = capacity ? capacity * 2 : 4; T* new_data = static_cast<T*>(CourseAllocator::allocate(new_capacity * sizeof(T))); for (size_t i = 0; i < size; ++i) { new (new_data + i) T(std::move(data[i])); data[i].~T(); } CourseAllocator::deallocate(data, capacity * sizeof(T)); data = new_data; capacity = new_capacity; } new (data + size) T(item); ++size; } T& operator[](size_t index) { return data[index]; } const T& operator[](size_t index) const { return data[index]; } size_t get_size() const { return size; } T* begin() { return data; } T* end() { return data + size; } const T* begin() const { return data; } const T* end() const { return data + size; } void remove(size_t index) { if (index >= size) return; data[index].~T(); for (size_t i = index; i < size - 1; ++i) { new (data + i) T(std::move(data[i + 1])); data[i + 1].~T(); } --size; } void clear() { for (size_t i = 0; i < size; ++i) { data[i].~T(); } size = 0; } }; // 程类 class Course { private: uint16_t id; // 2字节ID char name[32]; // 固定长度称 uint8_t credit; // 1字节学分 uint8_t max_capacity; // 1字节最大容量 uint8_t current_enrollment; // 1字节当前选人数 uint8_t day; // 上日(0-6代表周日至周六) uint8_t start_hour; // 开始小时(0-23) uint8_t end_hour; // 结束小时(0-23) public: Course() : id(0), credit(0), max_capacity(0), current_enrollment(0), day(0), start_hour(0), end_hour(0) { memset(name, 0, sizeof(name)); } Course(uint16_t id, const char* name, uint8_t credit, uint8_t max_capacity, uint8_t day, uint8_t start, uint8_t end) : id(id), credit(credit), max_capacity(max_capacity), current_enrollment(0), day(day), start_hour(start), end_hour(end) { strncpy(this->name, name, sizeof(this->name)-1); this->name[sizeof(this->name)-1] = '\0'; } // 自定义序列化方法 void serialize(std::fstream& fs) const { fs.write(reinterpret_cast<const char*>(this), sizeof(Course)); } // 反序列化 void deserialize(std::fstream& fs) { fs.read(reinterpret_cast<char*>(this), sizeof(Course)); } uint16_t get_id() const { return id; } const char* get_name() const { return name; } uint8_t get_credit() const { return credit; } uint8_t get_max_capacity() const { return max_capacity; } uint8_t get_current_enrollment() const { return current_enrollment; } uint8_t get_day() const { return day; } uint8_t get_start_hour() const { return start_hour; } uint8_t get_end_hour() const { return end_hour; } bool is_full() const { return current_enrollment >= max_capacity; } void enroll_student() { if (!is_full()) current_enrollment++; } void unenroll_student() { if (current_enrollment > 0) current_enrollment--; } }; // 学生类 class Student { private: uint32_t id; // 4字节学号 char name[20]; // 固定长度姓 LowLevelVector<uint16_t> course_ids; // 选ID集合 public: Student() : id(0) { memset(name, 0, sizeof(name)); } Student(uint32_t id, const char* name) : id(id) { strncpy(this->name, name, sizeof(this->name)-1); this->name[sizeof(this->name)-1] = '\0'; } // 自定义序列化 void serialize(std::fstream& fs) const { fs.write(reinterpret_cast<const char*>(&id), sizeof(id)); fs.write(reinterpret_cast<const char*>(name), sizeof(name)); uint16_t count = course_ids.get_size(); fs.write(reinterpret_cast<const char*>(&count), sizeof(count)); for (size_t i = 0; i < course_ids.get_size(); ++i) { fs.write(reinterpret_cast<const char*>(&course_ids[i]), sizeof(uint16_t)); } } // 反序列化 void deserialize(std::fstream& fs) { fs.read(reinterpret_cast<char*>(&id), sizeof(id)); fs.read(reinterpret_cast<char*>(name), sizeof(name)); uint16_t count; fs.read(reinterpret_cast<char*>(&count), sizeof(count)); for (uint16_t i = 0; i < count; ++i) { uint16_t course_id; fs.read(reinterpret_cast<char*>(&course_id), sizeof(course_id)); course_ids.push_back(course_id); } } uint32_t get_id() const { return id; } const char* get_name() const { return name; } const LowLevelVector<uint16_t>& get_course_ids() const { return course_ids; } bool add_course(uint16_t course_id) { for (size_t i = 0; i < course_ids.get_size(); i++) { if (course_ids[i] == course_id) { return false; // 已经选过该程 } } course_ids.push_back(course_id); return true; } bool remove_course(uint16_t course_id) { for (size_t i = 0; i < course_ids.get_size(); i++) { if (course_ids[i] == course_id) { course_ids.remove(i); return true; } } return false; } }; // 时间冲突检测系统(使用位图) class ScheduleConflictDetector { private: // 使用二维位图:7天,每天24小时,每小时用一个uint64_t表示(最多可以表示64门程) uint64_t schedule_map[7][24] = {{0}}; public: // 检查程在时间上是否与已有程冲突 bool check_conflict(const Course& course) const { uint8_t day = course.get_day(); uint8_t start = course.get_start_hour(); uint8_t end = course.get_end_hour(); for (uint8_t hour = start; hour < end; hour++) { if (schedule_map[day][hour] != 0) { return true; // 该时间段已有程 } } return false; } // 注册程时间 void register_course(const Course& course) { uint8_t day = course.get_day(); uint8_t start = course.get_start_hour(); uint8_t end = course.get_end_hour(); uint16_t id = course.get_id(); for (uint8_t hour = start; hour < end; hour++) { schedule_map[day][hour] |= (1ULL << (id % 64)); // 使用程ID的低6位 } } // 清除程时间 void unregister_course(const Course& course) { uint8_t day = course.get_day(); uint8_t start = course.get_start_hour(); uint8_t end = course.get_end_hour(); uint16_t id = course.get_id(); for (uint8_t hour = start; hour < end; hour++) { schedule_map[day][hour] &= ~(1ULL << (id % 64)); } } }; // 选系统核心 class CourseSelectionSystem { private: LowLevelVector<Course> courses; LowLevelVector<Student> students; ScheduleConflictDetector conflict_detector; public: // 添加程 bool add_course(const Course& course) { if (conflict_detector.check_conflict(course)) { return false; // 时间冲突 } conflict_detector.register_course(course); courses.push_back(course); return true; } // 学生 bool enroll_course(uint32_t student_id, uint16_t course_id) { Student* student = find_student(student_id); Course* course = find_course(course_id); if (!student || !course) { return false; } if (course->is_full()) { return false; } if (!student->add_course(course_id)) { return false; // 已经选过 } course->enroll_student(); return true; } // 学生退 bool unenroll_course(uint32_t student_id, uint16_t course_id) { Student* student = find_student(student_id); Course* course = find_course(course_id); if (!student || !course) { return false; } if (!student->remove_course(course_id)) { return false; // 未选该程 } course->unenroll_student(); return true; } // 查找学生 Student* find_student(uint32_t student_id) { for (size_t i = 0; i < students.get_size(); i++) { if (students[i].get_id() == student_id) { return &students[i]; } } return nullptr; } // 查找程 Course* find_course(uint16_t course_id) { for (size_t i = 0; i < courses.get_size(); i++) { if (courses[i].get_id() == course_id) { return &courses[i]; } } return nullptr; } // 添加学生 void add_student(const Student& student) { students.push_back(student); } // 获取程列表 const LowLevelVector<Course>& get_courses() const { return courses; } // 获取学生列表 const LowLevelVector<Student>& get_students() const { return students; } // 序列化整个系统 void serialize(const char* filename) { std::fstream fs(filename, std::ios::binary | std::ios::out); if (!fs) { std::cerr << "无法打开文件进行写入: " << filename << std::endl; return; } // 写入魔数标识和版本 uint32_t magic = 0x43535359; // 'CSSY' uint16_t version = 0x0100; // 版本1.0 fs.write(reinterpret_cast<const char*>(&magic), sizeof(magic)); fs.write(reinterpret_cast<const char*>(&version), sizeof(version)); // 写入程数量和学生数量 uint32_t course_count = courses.get_size(); uint32_t student_count = students.get_size(); fs.write(reinterpret_cast<const char*>(&course_count), sizeof(course_count)); fs.write(reinterpret_cast<const char*>(&student_count), sizeof(student_count)); // 写入程 for (size_t i = 0; i < course_count; ++i) { courses[i].serialize(fs); } // 写入学生 for (size_t i = 0; i < student_count; ++i) { students[i].serialize(fs); } fs.close(); } // 反序列化 void deserialize(const char* filename) { std::fstream fs(filename, std::ios::binary | std::ios::in); if (!fs) { std::cerr << "无法打开文件进行读取: " << filename << std::endl; return; } uint32_t magic; uint16_t version; fs.read(reinterpret_cast<char*>(&magic), sizeof(magic)); fs.read(reinterpret_cast<char*>(&version), sizeof(version)); if (magic != 0x43535359 || version != 0x0100) { std::cerr << "文件格式不匹配或版本不支持" << std::endl; return; } uint32_t course_count; uint32_t student_count; fs.read(reinterpret_cast<char*>(&course_count), sizeof(course_count)); fs.read(reinterpret_cast<char*>(&student_count), sizeof(student_count)); // 读取程 for (uint32_t i = 0; i < course_count; ++i) { Course course; course.deserialize(fs); courses.push_back(course); conflict_detector.register_course(course); } // 读取学生 for (uint32_t i = 0; i < student_count; ++i) { Student student; student.deserialize(fs); students.push_back(student); } fs.close(); } }; // 控制台界面 class ConsoleUI { private: CourseSelectionSystem system; public: void show_main_menu() { while (true) { std::cout << "\n===== 学生系统 =====\n"; std::cout << "1. 管理员模式\n"; std::cout << "2. 学生模式\n"; std::cout << "3. 保存系统状态\n"; std::cout << "4. 加载系统状态\n"; std::cout << "0. 退出\n"; std::cout << "=======================\n"; std::cout << "请选择: "; int choice; std::cin >> choice; switch (choice) { case 1: show_admin_menu(); break; case 2: show_student_menu(); break; case 3: system.serialize("system.dat"); std::cout << "系统状态已保存.\n"; break; case 4: system.deserialize("system.dat"); std::cout << "系统状态已加载.\n"; break; case 0: return; default: std::cout << "无效选择!\n"; } } } private: void show_admin_menu() { while (true) { std::cout << "\n===== 管理员菜单 =====\n"; std::cout << "1. 添加程\n"; std::cout << "2. 添加学生\n"; std::cout << "3. 查看程列表\n"; std::cout << "4. 查看学生列表\n"; std::cout << "0. 返回主菜单\n"; std::cout << "=======================\n"; std::cout << "请选择: "; int choice; std::cin >> choice; switch (choice) { case 1: add_course(); break; case 2: add_student(); break; case 3: display_courses(); break; case 4: display_students(); break; case 0: return; default: std::cout << "无效选择!\n"; } } } void show_student_menu() { uint32_t student_id; std::cout << "请输入学生ID: "; std::cin >> student_id; if (!system.find_student(student_id)) { std::cout << "学生ID不存在!\n"; return; } while (true) { std::cout << "\n===== 学生菜单 (" << student_id << ") =====\n"; std::cout << "1. 查看可选程\n"; std::cout << "2. 选\n"; std::cout << "3. 退\n"; std::cout << "4. 查看已选程\n"; std::cout << "0. 返回主菜单\n"; std::cout << "============================\n"; std::cout << "请选择: "; int choice; std::cin >> choice; switch (choice) { case 1: display_available_courses(student_id); break; case 2: enroll_course(student_id); break; case 3: unenroll_course(student_id); break; case 4: display_enrolled_courses(student_id); break; case 0: return; default: std::cout << "无效选择!\n"; } } } void add_course() { uint16_t id; char name[32]; uint8_t credit, capacity, day, start, end; std::cout << "输入程ID: "; std::cin >> id; std::cout << "输入称: "; std::cin.ignore(); std::cin.getline(name, sizeof(name)); std::cout << "输入学分: "; std::cin >> credit; std::cout << "输入最大容量: "; std::cin >> capacity; std::cout << "输入上日(0-6 周日-周六): "; std::cin >> day; std::cout << "输入开始小时(0-23): "; std::cin >> start; std::cout << "输入结束小时(0-23): "; std::cin >> end; Course course(id, name, credit, capacity, day, start, end); if (system.add_course(course)) { std::cout << "程添加成功!\n"; } else { std::cout << "程添加失败(时间冲突)!\n"; } } void add_student() { uint32_t id; char name[20]; std::cout << "输入学生ID: "; std::cin >> id; std::cout << "输入学生: "; std::cin.ignore(); std::cin.getline(name, sizeof(name)); Student student(id, name); system.add_student(student); std::cout << "学生添加成功!\n"; } void display_courses() { const auto& courses = system.get_courses(); std::cout << "\n程列表:\n"; std::cout << "ID\t称\t学分\t容量\t已选\t时间\n"; for (size_t i = 0; i < courses.get_size(); i++) { const Course& c = courses[i]; std::cout << c.get_id() << "\t" << c.get_name() << "\t" << static_cast<int>(c.get_credit()) << "\t" << static_cast<int>(c.get_max_capacity()) << "\t" << static_cast<int>(c.get_current_enrollment()) << "\t" << "周" << static_cast<int>(c.get_day()) << " " << static_cast<int>(c.get_start_hour()) << ":00-" << static_cast<int>(c.get_end_hour()) << ":00\n"; } } void display_students() { const auto& students = system.get_students(); std::cout << "\n学生列表:\n"; std::cout << "ID\t姓\n"; for (size_t i = 0; i < students.get_size(); i++) { const Student& s = students[i]; std::cout << s.get_id() << "\t" << s.get_name() << "\n"; } } void display_available_courses(uint32_t student_id) { const auto& courses = system.get_courses(); const Student* student = system.find_student(student_id); std::cout << "\n可选程列表:\n"; std::cout << "ID\t称\t学分\t容量\t已选\t时间\n"; for (size_t i = 0; i < courses.get_size(); i++) { const Course& c = courses[i]; // 检查是否已选 bool enrolled = false; const auto& enrolled_courses = student->get_course_ids(); for (size_t j = 0; j < enrolled_courses.get_size(); j++) { if (enrolled_courses[j] == c.get_id()) { enrolled = true; break; } } if (!enrolled && !c.is_full()) { std::cout << c.get_id() << "\t" << c.get_name() << "\t" << static_cast<int>(c.get_credit()) << "\t" << static_cast<int>(c.get_max_capacity()) << "\t" << static_cast<int>(c.get_current_enrollment()) << "\t" << "周" << static_cast<int>(c.get_day()) << " " << static_cast<int>(c.get_start_hour()) << ":00-" << static_cast<int>(c.get_end_hour()) << ":00\n"; } } } void display_enrolled_courses(uint32_t student_id) { const Student* student = system.find_student(student_id); if (!student) { std::cout << "学生不存在!\n"; return; } std::cout << "\n已选程列表:\n"; std::cout << "ID\t称\t学分\t时间\n"; const auto& course_ids = student->get_course_ids(); for (size_t i = 0; i < course_ids.get_size(); i++) { Course* course = system.find_course(course_ids[i]); if (course) { std::cout << course->get_id() << "\t" << course->get_name() << "\t" << static_cast<int>(course->get_credit()) << "\t" << "周" << static_cast<int>(course->get_day()) << " " << static_cast<int>(course->get_start_hour()) << ":00-" << static_cast<int>(course->get_end_hour()) << ":00\n"; } } } void enroll_course(uint32_t student_id) { uint16_t course_id; std::cout << "输入要选的程ID: "; std::cin >> course_id; if (system.enroll_course(student_id, course_id)) { std::cout << "选成功!\n"; } else { std::cout << "选失败(程已满、不存在或已选)!\n"; } } void unenroll_course(uint32_t student_id) { uint16_t course_id; std::cout << "输入要退的程ID: "; std::cin >> course_id; if (system.unenroll_course(student_id, course_id)) { std::cout << "退成功!\n"; } else { std::cout << "退失败(未选该程或程不存在)!\n"; } } }; int main() { ConsoleUI ui; ui.show_main_menu(); return 0; }这代码中有些问题比如size_t转换成unit16_t可能丢失数据我希望解决代码中类似问题
06-23
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值