SQL - 连接同一行中的多个列

Our school's ERP has a nasty database structure to it and since it is not normalized correctly, I have an issue with joining on the same table multiple times.

我们学校的ERP有一个讨厌的数据库结构,因为它没有正确规范化,我有一个多次加入同一个表的问题。

The DegreeHistory table has these columns in one row entry for a person:

DegreeHistory表将这些列放在一个人的一行条目中:

|Major1|Major2|Major3|Minor1|Minor2|Minor3|
-------|------|------|------|------|------|
 CMPT                 BUSI

There is another table DegreeDescription:

还有另一个表DegreeDescription:

|DegreeCode|DegreeDesc      |
-----------|-----------------
CMPT       |Computer Science
BUSI       |Business

I want a query shows a student's degree history information but skips the degree codes and shows the degree description instead. Is there a way I can do this other than:

我想查询显示学生的学位历史信息,但跳过学位代码并显示学位描述。有没有办法可以做到这一点:

SELECT dd.DegreeDesc, dd1.DegreeDesc FROM DegreeHistory dh
LEFT JOIN DegreeDescription dd ON dd.DegreeCode = dh.Major1
LEFT JOIN DegreeDescription dd1 ON dd1.DegreeCode = dh.Major2 ...

For each of the possible majors, minors, concentrations, certifications, etc... Seems like a large and ugly query (though simple to do).

对于每个可能的专业,未成年人,集中,认证等...似乎是一个大而丑陋的查询(虽然很简单)。

1 个解决方案

#1

You are in the right track, just a little change...:

你走在正确的轨道上,只是一点变化......:

SELECT dd.DegreeDesc, dd1.DegreeDesc FROM DegreeHistory dh
LEFT JOIN DegreeDescription ON DegreeCode = dh.Major1 dd
LEFT JOIN DegreeDescription ON DegreeCode = dh.Major2 dd1 ...

Certainly it's ugly but that's what you get with non-normalized structures :-(

当然它很丑,但这是你得到的非标准化结构:-(

#1