如何将表中的 1 个 id 字段连接到 2 个不同的表列

Question

我正在制作一个学校管理程序，在该程序中我想将多个表连接到学生表，我想将城市 ID 字段用于学生出生地和表中的实际地址，但我无法弄清楚怎么做。SELECT Students.StudentID, Students.Name, Students.Birthday, Students.MothersName, Classes.ClassName, Cities.Name, Cities.Name,PostalCode.PostalCode, Street.StreetName, Students.NumberFROM StudentsINNER JOIN Classes ON Students.ClassID = Classes.ClassIDINNER JOIN Cities ON Students.BirthPlaceID = Cities.CityIDINNER JOIN Cities ON Students.CityID = Cities.CityIDINNER JOIN PostalCode ON Students.PostalCodeID = PostalCode.PostalCodeIDINNER JOIN Utca ON Students.StreetID = Streets.StreetID我已经尝试过这种方式，但只会导致错误。

Answer 1

您在选择中为表创建别名，然后您可以使用 2 个不同的标识访问同一个表。我对您的 select 语句中的值执行了相同的操作（创建别名），以确定哪个城市是哪个城市。

SELECT Students.StudentID, Students.Name, Students.Birthday, Students.MothersName, Classes.ClassName, StudentBirthCities.Name as StudentBirthCityName, StudentCity.Name as StudentCityName,

PostalCode.PostalCode, Street.StreetName, Students.Number

FROM Students

INNER JOIN Classes ON Students.ClassID = Classes.ClassID

INNER JOIN Cities StudentBirthCities ON Students.BirthPlaceID = StudentBirthCities.CityID

INNER JOIN Cities StudentCity ON Students.CityID = StudentCity.CityID

INNER JOIN PostalCode ON Students.PostalCodeID = PostalCode.PostalCodeID

INNER JOIN Utca ON Students.StreetID = Streets.StreetID