我有这两个表,我试图加入他们两个多次,但失败了。下面是表格。
表1受试者:
+------------+----------+
| ccaSubject | ccaPrice |
+------------+----------+
| Chess | 100 |
| Badminton | 300 |
| Dancing | 200 |
| Singing | 200 |
| Football | 250 |
| Fitness | 600 |
| Robotics | 1000 |
+------------+----------+
表rispenrollment
+--------------------+-----------+-----------+----------+
| studentIdentifier | firstCCA | secondCCA | thirdCCA |
+--------------------+-----------+-----------+----------+
| elly@example.com | Robotics | Singing | Dancing |
| mike@example.com | Chess | Singing | Robotics |
| tom@example.com | Badminton | Dancing | Chess |
| peter@example.com | Football | Fitness | Robotics |
| andrew@example.com | Robotics | Singing | Chess |
+--------------------+-----------+-----------+----------+
我希望我的输出是这样的:
+--------------------+-----------+-----------+----------+-----------+-----------+-----------+
| studentIdentifier | firstCCA | secondCCA | thirdCCA | CCA1price | CCA2price | CCA3price |
+--------------------+-----------+-----------+----------+-----------+-----------+-----------+
| elly@example.com | Robotics | Singing | Dancing | 1000 | 200 | 200 |
| mike@example.com | Chess | Singing | Robotics | 100 | 200 | 1000 |
| tom@example.com | Badminton | Dancing | Chess | 300 | 200 | 100 |
| peter@example.com | Football | Fitness | Robotics | 250 | 600 | 1000 |
| andrew@example.com | Robotics | Singing | Chess | 1000 | 200 | 100 |
+--------------------+-----------+-----------+----------+-----------+-----------+-----------+
从我的代码中,我只能使用一次内部连接并获得 CCA1price
,我无法得到 cca2price
以及 cca3price
因为错误一直在说 Same aliases
.
1条答案
按热度按时间tnkciper1#
你可以加入
rispEnrollment
餐桌ccaSubjects
你需要多少次就坐多少次。在这种情况下,您可以加入三次,以便为三个主题列中的每一列引入price列。注意,我在这里使用左连接,以防
rispEnrollment
表的主题可能与表中的任何内容都不匹配ccaSubjects
table。