两个方向各是几对几
从数据上验证这是多对多:输出三个数:选课最多的学生选了几门、选课人数最多的课有几人、中间表一共几行(用 / 隔开)。
enroll_flat(一张宽表,什么都塞在里面) student phone course teacher room 阿岚 111 数学 王老师 A101 阿岚 111 英语 李老师 B202 小满 222 数学 王老师 A101 阿泰 333 英语 李老师 B202 小满 222 物理 张老师 C303 南风 444 数学 王老师 A101 拆开之后的三张 student(id, name, phone) 4 行 course (id, name, teacher, room) 3 行 enroll (student_id, course_id) 6 行
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE enroll_flat (student TEXT, phone TEXT, course TEXT, teacher TEXT, room TEXT);
CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, phone TEXT NOT NULL);
CREATE TABLE course (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE,
teacher TEXT NOT NULL, room TEXT NOT NULL);
CREATE TABLE enroll (student_id INTEGER, course_id INTEGER,
PRIMARY KEY (student_id, course_id));
""")
db.executemany("INSERT INTO enroll_flat VALUES (?,?,?,?,?)", [
("阿岚", "111", "数学", "王老师", "A101"),
("阿岚", "111", "英语", "李老师", "B202"),
("小满", "222", "数学", "王老师", "A101"),
("阿泰", "333", "英语", "李老师", "B202"),
("小满", "222", "物理", "张老师", "C303"),
("南风", "444", "数学", "王老师", "A101")])
db.executemany("INSERT INTO student VALUES (?,?,?)", [
(1, "阿岚", "111"), (2, "小满", "222"), (3, "阿泰", "333"), (4, "南风", "444")])
db.executemany("INSERT INTO course VALUES (?,?,?,?)", [
(1, "数学", "王老师", "A101"), (2, "英语", "李老师", "B202"),
(3, "物理", "张老师", "C303")])
db.executemany("INSERT INTO enroll VALUES (?,?)", [
(1, 1), (1, 2), (2, 1), (3, 2), (2, 3), (4, 1)])
a = db.execute("SELECT COUNT(*) c FROM enroll GROUP BY student_id "
"ORDER BY c DESC LIMIT 1").fetchone()[0]
b = db.execute("SELECT COUNT(*) c FROM enroll GROUP BY course_id "
"ORDER BY c DESC LIMIT 1").fetchone()[0]
c = db.execute("SELECT COUNT(*) FROM enroll").fetchone()[0]
print(str(a) + "/" + str(b) + "/" + str(c))
全部评论