我正在從事一個攝影專案，但在連接表和從 mysql 資料庫中檢索資料時遇到了一些問題。我為這個專案創建了兩個表。一張名為cm_team 的表用于團隊成員，另一張名為cm_events 的表用于攝影活動。假設拍攝一個活動，我們需要6 個人，該人的id 存盤在cm_events表中。
從上面的圖片中可以看出..我將 cm_team 成員的 id 存盤在 cm_events 表中.. 我希望在 cm_events 表中各個突出顯示的欄位中獲取團隊成員的姓名..非常感謝任何幫助.
例如我想要的輸出應該是：而不是在 team_lead 標題下的 5，我應該得到對應于 5 的名稱，即 Arjun
uj5u.com熱心網友回復：
像這樣的東西？（比子查詢更干凈、更快）
SELECT
`event`.client_name,
`event`.client_number,
# some more event cols ..
`team_lead`.`cm_name` AS `team_lead`,
`candid_photo`.`cm_name` AS `candid_photo`,
`candid_video`.`cm_name` AS `candid_video`,
`traditional_photo`.`cm_name` AS `traditional_photo`,
`traditional_video`.`cm_name` AS `traditional_video`,
`helper`.`cm_name` AS `helper`
FROM cm_events `event`
JOIN cm_team `team_lead` ON `team_lead`.`cm_code` = `event`.`team_lead`
JOIN cm_team `candid_photo` ON `candid_photo`.`cm_code` = `event`.`candid_photo`
JOIN cm_team `candid_video` ON `candid_video`.`cm_code` = `event`.`candid_video`
JOIN cm_team `traditional_photo` ON `traditional_photo`.`cm_code` = `event`.`traditional_photo`
JOIN cm_team `traditional_video` ON `traditional_video`.`cm_code` = `event`.`traditional_video`
JOIN cm_team `helper` ON `helper`.`cm_code` = `event`.`helper`
uj5u.com熱心網友回復：
使用子查詢
DROP TABLE IF EXISTS T,t1;
CREATE TABLE T (
id int, name varchar(10));
insert into t values
(1 , 'aaa'),
(2 , 'bbb');
create table t1 (
id int, team_lead int,team_a int);
insert into t1 values
(1,1,2),
(2,2,2);
select t1.id, (select t.name from t where t.id = t1.team_lead) team_lead,
(select t.name from t where t.id = t1.team_a) team_a
from t1;
------ ----------- --------
| id | team_lead | team_a |
------ ----------- --------
| 1 | aaa | bbb |
| 2 | bbb | bbb |
------ ----------- --------
2 rows in set (0.001 sec)
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/365532.html