(已解決)MySQL 查詢指令請教

mysql 查詢指令 mysql select

camabula 2020-12-30 19:23:09 ‧ 1324 瀏覽

分享至

MySQL裡有兩個表格如下：

table1
+--------------+------------+-----+------------+------------+
| fd1          | user1      | fd3 | fd4        | user2      |
+--------------+------------+-----+------------+------------+
| 100000000001 | 3396409604 |   2 | 2020-12-29 | 151475666  |
| 100000000002 | 151475666  |   3 | 2020-12-30 | 3396409604 |
| 100000000003 | 3396409604 |   1 | 2020-12-30 | 151475666  |
| 100000000004 | 151475666  |   5 | 2020-12-30 | 3396409604 |
+--------------+------------+-----+------------+------------+

table2
+-----------+------------+
| Name      | Code       |
+-----------+------------+
| 趙大明    | 151475666  |
| 錢小華    | 3396409604 |
+-----------+------------+

請問各位前輩高手們，我要怎麼下查詢指令把table1裡的user1及user2欄位參照table2的Code呈現出Name來呢？

我想要呈現的結果
+--------------+-------+-----+------------+---------+
| fd1          | user1 | fd3 | fd4        | user2   |
+--------------+-------+-----+------------+---------+
| 100000000001 | 錢小華 |   2 | 2020-12-29 | 趙大明  |
| 100000000002 | 趙大明 |   3 | 2020-12-30 | 錢小華  |
| 100000000003 | 錢小華 |   1 | 2020-12-30 | 趙大明  |
| 100000000004 | 趙大明 |   5 | 2020-12-30 | 錢小華  |
+--------------+-------+-----+------------+---------+

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2020-12-30 20:19:24

最佳解答

CREATE TABLE table1(fd1 TEXT(40), user1 TEXT(40), fd3 TEXT(40) ,fd4 TEXT(40),user2 TEXT(40));  

INSERT INTO table1 (fd1,user1,fd3,fd4,user2)
VALUES
('100000000001','3396409604','2','2020-12-29','151475666'),
('100000000002','151475666','3','2020-12-30','3396409604'),
('100000000003','3396409604','1','2020-12-30','151475666'),
('100000000004','151475666','5','2020-12-30','3396409604');

CREATE TABLE table2(Name TEXT(40),Code TEXT(40));

INSERT INTO table2 (Name,Code)
VALUES
('趙大明','151475666'),
('錢小華','3396409604');

SELECT table1.fd1,A.Name AS user1,table1.fd3,table1.fd4,B.Name AS user2
FROM table1 
LEFT JOIN table2 AS A ON A.Code=table1.user1
LEFT JOIN table2 AS B ON B.Code=table1.user2
ORDER BY table1.fd1

Demo

回應 1
分享
檢舉

camabula iT邦新手 3 級 ‧ 2020-12-31 12:17:23 檢舉

謝謝您的回覆。

登入發表回應

wrxue

iT邦好手 1 級 ‧ 2020-12-30 20:08:56

SELECT t1.fd1, u1.Name AS user1, t1.fd3, t1.fd4, u2.Name AS user2
FROM table1 AS t1
INNER JOIN table2 AS u1
ON t1.user1 = u1.Code
INNER JOIN table2 AS u2
ON t1.user2 = u2.Code