时间戳

首页 > 解决方案 > 在表中多次加入同一字段

mysql - 在表中多次加入同一字段

问题描述

我有这 2 张桌子，我试图多次加入它们但失败了。下面是表格。

表 cca 主题：

+------------+----------+
| ccaSubject | ccaPrice |
+------------+----------+
| Chess      |      100 |
| Badminton  |      300 |
| Dancing    |      200 |
| Singing    |      200 |
| Football   |      250 |
| Fitness    |      600 |
| Robotics   |     1000 |
+------------+----------+

表risp注册 

+--------------------+-----------+-----------+----------+
| 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，而我无法再获得cca2priceand cca3price，因为错误一直在说Same aliases。

标签： mysql

解决方案

您可以根据需要多次rispEnrollment将表格加入表格。ccaSubjects在这种情况下，您可以加入 3 次以分别为三个主题栏引入价格栏。

SELECT
    t1.studentIdentifier,
    t1.firstCCA,
    t1.secondCCA,
    t1.thirdCCA,
    t2.ccaPrice AS CCA1price,
    t3.ccaPrice AS CCA2price,
    t4.ccaPrice AS CCA3price
FROM rispEnrollment t1
LEFT JOIN ccaSubjects t2
    ON t1.firstCCA = t2.ccaSubject
LEFT JOIN ccaSubjects t3
    ON t1.secondCCA = t3.ccaSubject
LEFT JOIN ccaSubjects t4
    ON t1.thirdCCA = t4.ccaSubject;

请注意，我在这里使用左连接以防rispEnrollment表中的主题可能与表中的任何内容都不匹配ccaSubjects。

推荐阅读