如何将表中的 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.Number

FROM Students

INNER JOIN Classes ON Students.ClassID = Classes.ClassID

INNER JOIN Cities ON Students.BirthPlaceID = Cities.CityID

INNER JOIN Cities ON Students.CityID = Cities.CityID

INNER JOIN PostalCode ON Students.PostalCodeID = PostalCode.PostalCodeID

INNER 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.NumberFROM StudentsINNER JOIN Classes ON Students.ClassID = Classes.ClassIDINNER JOIN Cities StudentBirthCities ON Students.BirthPlaceID = StudentBirthCities.CityIDINNER JOIN Cities StudentCity ON Students.CityID = StudentCity.CityIDINNER JOIN PostalCode ON Students.PostalCodeID = PostalCode.PostalCodeIDINNER JOIN Utca ON Students.StreetID = Streets.StreetID