python - 如何以最简单的方式连接两个没有公共(public)键的表？

Question

这是我创建的数据库:

students = [('TOM', 6120, 85),
           ('Jerry', 6110,86),
           ('Spike', 6120,55),
           ('Tyke',6110,73),
           ('Butch',6110,89),
           ('Toodle',6120,76)]
courses = [(6110,'Data Science I', 'LSB105'),
          (6120,'Data Science II', 'LSB109')]
grading = [('A', 90, 100),
          ('B', 80,90),
          ('C',70,80)]

import sqlite3
conn = sqlite3.connect('example3.db')
c = conn.cursor()

c.execute('CREATE TABLE students(name TEXT, courseid INTEGER, score INTEGER)') #create a table
c.executemany('INSERT INTO students VALUES(?,?,?)', students)

c.execute('CREATE TABLE courses(courseid INTEGER, name TEXT, classroom TEXT)') #create a table
c.executemany('INSERT INTO courses VALUES(?,?,?)', courses)

c.execute('CREATE TABLE gradingscheme(letter TEXT, lower REAL, upper REAL)') #create a table
c.executemany('INSERT INTO gradingscheme VALUES(?,?,?)', grading)

conn.commit()
conn.close()

这就是我最终想要的: final result

我做了3步，我觉得我的方法太复杂了:

c.execute('''
CREATE view part1 AS
SELECT s.name,s.courseid,c.name AS coursename,classroom,score
FROM students AS s, courses AS c
WHERE s.courseid=c.courseid 
''')

#Create another view "part2" to combine "part1" and table: "gradingscheme"
c.execute('''
CREATE view part2 AS
SELECT * FROM part1 , (SELECT * FROM gradingscheme AS g)
''')

#Get the final results
c.execute('''
SELECT name, courseid, coursename, classroom, score, letter
FROM part2 
WHERE score BETWEEN lower AND upper
''')

那么，有人可以帮我缩短我的代码吗？感谢您的帮助!

Answer 1

在这种情况下不需要 View 。您可以直接加入，如下。我已使用左连接进行评分方案来处理不在评分方案中定义的范围内的分数。例如，我对低于 70 分的成绩使用“D”。如果您不需要它并且所有范围都将在评分方案表中给出，您可以使用 JOIN 本身而不是 LEFT JOIN

SELECT s.name, s.courseid, 
c.name AS coursename, 
c.classroom, s.score, 
COALESCE(g.letter, 'D') AS grade
FROM students s
JOIN courses c
ON (s.courseid=c.courseid)
LEFT JOIN gradingscheme g
ON (s.score >= g.lower AND s.score <= g.upper)

谢谢，
拉维