mysql - 当我们在表中仅使用主题 ID 时如何从表中选择主题名称

Question

当我们只使用表中的主题 ID 而我们正在使用另一个表主题主时，如何从表中选择主题名称。

Table StudentMaster
--------------------------
StudentId   int Unchecked
StudentName varchar(50) Checked
Subject1    int Checked
Subject2    int Checked
Subject3    int Checked

Sample Record
------------------------------------
ID  Name    Subject1   Subject2   Subject3
1   Mohan      1           2         4
2   Sohan      3           2         5
3   Neha       5           1         2


Table Subject
-----------------------
SubjectId   int Unchecked
SubjectName varchar(150)    Checked

Sample Record
-----------------
SubjectId        SubjectName
   1                Hindi
   2                English
   3                Physics
   4                Chemistry
   5                Maths

如何获取Sohan、Mohan、Neha分别选择了哪个科目？？？

我想要这样的结果

StudentId   StudentName   Subject1    Subject2    Subject3
    1       Mohan         Hindi       English     Chemistry
    2       Sohan         Physics     English     Maths
    3       Neha          Maths       Hindi       English

Answer 1

试试这个:

SELECT
    SM.StudentId,
    SM.StudentName,
    S1.SubjectName AS "Subject1",
    S2.SubjectName AS "Subject2",
    S3.SubjectName AS "Subject3"
FROM StudentMaster SM
LEFT JOIN Subject S1
     S1.SubjectId = SM.Subject1
LEFT JOIN Subject S2
     S2.SubjectId = SM.Subject2
LEFT JOIN Subject S3
     S3.SubjectId = SM.Subject3