Mysql
如何填寫和重命名 2 表連接中的列？
我正在為機器零件創建一個數據庫，Java 程序將使用該數據庫進行進一步處理。
我有 2 張表，一張用於訂單，一張用於組件：
訂單表：
+----+------------------+-------+--------+---------+---------------+-------+ | | | | | | | | | ID | equipment_number | brake | buffer | limiter | smokeDetector | (...) | | | | | | | | | +--------------------------------------------------------------------------+ | 1 | 1111 | 1 | 2 | 3 | 4 | | | 2 | 2222 | 98 | 2 | 96 | 99 | | | 3 | 3333 | 1 | 105 | 205 | 104 | | | 4 | 4444 | 102 | 117 | 147 | 200 | | +----+------------------+-------+--------+---------+---------------+-------+compslist 表：
+----+------------------+----------------+-------------+-----+ | | | | | | | ID | type | manufacturer | certificate | (..)| | | | | | | +------------------------------------------------------------+ | 1 | brake | bosch | xyz | | | 2 | buffer | bosch | 123 | | | 3 | limiter | mercedes | abc | | | 4 | smokeDetector | mercedes | efg | | +----+------------------+----------------+-------------+-----+現在我想將這兩個表連接在一起，並通過“填充”compslist 表列中的資訊來“擴展”orders 表中的“brake、buffer、limiter、smokeDetector”列。
所以我想要的結果是這樣的：
想要的結果：
+----+------------------+-----------------+--------------------+-------------------+-------------+---------------------+--------------------+-------+ | | | | | | | | | | | ID | equipment_number | brake_type | brake_manufacturer | brake_certificate | buffer_type | buffer_manufacturer | buffer_certificate | (...) | | | | | | | | | | | +---------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 1111 | brake | bosch | xyz | buffer | bosch | 123 | | | 2 | 2222 | brake | bmw | zut | buffer | bosch | 123 | | | 3 | 3333 | brake | bosch | xyz | buffer | toyota | ert | | | 4 | 4444 | brake | mercedes | gbn | buffer | mercedes | dfg | | +----+------------------+-----------------+--------------------+-------------------+-------------+---------------------+--------------------+-------+我試圖通過對組件（剎車、緩衝區、…）進行多次連接來實現這一點：
SELECT * FROM orders JOIN compslist AS A ON orders.brake = A.compslist_id JOIN compslist AS B ON orders.buffer = B.compslist_id JOIN compslist AS C ON orders.limiter = C.compslist_id JOIN compslist AS D ON orders.smokeDetector = D.compslist_id;但這會導致結果，我從每個表中為每個組件獲取所有列：
我現在得到的結果：
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |order_id | equipment_number | brake | buffer | limiter | smokeDetector | compslist_id | type | manufacturer | certificate | compslist_id | type | manufacturer | certificate | compslist_id | type | manufacturer | certificate | compslist_id | type | manufacturer | certificate | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2 |444 | 1 | 2 | 3 | 4 | 1 | brake | bosch | xyz | 2 | buffer | bosch | 123 | 3 | limiter | vw | abc | 4 | smokeDetector | vw | efg | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+這包含我想要的資訊，但比我需要的要長得多。現在我還有幾個名為“type”、“manufacturer”等的列，這使得在 Java 中通過它們的名稱來獲取這些列變得更加困難。
那麼有沒有辦法將第二個表中的資訊“填充”到第一個表中，同時為列創建新名稱？
嗯……我自己找到了答案。在 Roman Hocke 提醒我之後，您也可以為列使用別名：
SELECT O.order_id, O.equipment_number, C.type AS brake_type, C.manufacturer AS brake_manufactureer, C.certificate AS brake_certificate, C2.type AS buffer_type, C2.manufacturer AS buffer_manufacturer, C2.certificate AS buffer_certificate FROM orders AS O INNER JOIN compslist AS C ON O.brake = C.compslist_id INNER JOIN compslist AS C2 ON O.buffer = C2.compslist_id;這給了我我正在尋找的結果：
+----+------------------+-----------------+--------------------+-------------------+-------------+---------------------+--------------------+-------+ | | | | | | | | | | | ID | equipment_number | brake_type | brake_manufacturer | brake_certificate | buffer_type | buffer_manufacturer | buffer_certificate | (...) | | | | | | | | | | | +---------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 1111 | brake | bosch | xyz | buffer | bosch | 123 | | | 2 | 2222 | brake | bmw | zut | buffer | bosch | 123 | | | 3 | 3333 | brake | bosch | xyz | buffer | toyota | ert | | | 4 | 4444 | brake | mercedes | gbn | buffer | mercedes | dfg | | +----+------------------+-----------------+--------------------+-------------------+-------------+---------------------+--------------------+-------+為簡潔起見，我省略了限制器和煙霧探測器等其他組件，但是這兩個組件的方法是相同的。