python - 如何以最简单的方式联接没有公用 key 的2个表？

这是我创建的数据库：

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

我做到了三步，我认为我的方法太复杂了：

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
''')

因此，有人可以帮助我缩短代码吗？感谢您的帮助！

最佳答案

在这种情况下，不需要视图。您可以按以下方式直接加入。我已经对gradingscheme使用左连接来处理不在gradingscheme中定义的范围内的分数。例如，我用分数'D'表示分数小于70。如果您不需要它，并且所有范围都将在gradingscheme表中给出，则可以使用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)

谢谢，
拉维