⚠️ 冗余那一列忘了同步
在课程表上冗余一列"选课人数"并填好,然后插一条新的选课记录、但忘了同步那一列。输出两个数:冗余列写的人数、真实算出来的人数(用 / 隔开)。
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)])
db.execute("ALTER TABLE course ADD COLUMN cnt INTEGER")
db.execute("UPDATE course SET cnt = "
"(SELECT COUNT(*) FROM enroll WHERE course_id = course.id)")
db.execute("INSERT INTO enroll VALUES (3, 1)") # 阿泰也选了数学,但没同步 cnt
a = db.execute("SELECT cnt FROM course WHERE name = '数学'").fetchone()[0]
b = db.execute("SELECT COUNT(*) FROM enroll e JOIN course c "
"ON c.id = e.course_id WHERE c.name = '数学'").fetchone()[0]
print(str(a) + "/" + str(b))
全部评论