iT邦幫忙

1

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

  • 分享至 

  • xImage

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 }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

3
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

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

謝謝您的回覆。/images/emoticon/emoticon41.gif
/images/emoticon/emoticon42.gif

0
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
camabula iT邦新手 2 級 ‧ 2020-12-31 12:08:01 檢舉

謝謝您的回覆。/images/emoticon/emoticon41.gif

我要發表回答

立即登入回答