iT邦幫忙

0

[SQL] 該怎麼歸成一類

CREATE TABLE table1 (
id varchar(10),
name varchar(10)
)

CREATE TABLE table2 (
id varchar(10),
name varchar(10)
)
  
  INSERT INTO table1 VALUES ('1', 'John'), ('1', 'Merry'), ('2', 'Ted'), ('3', 'Jack'), ('3', 'Zed');
  INSERT INTO table2 VALUES ('1234', 'John'), ('1234', 'Peter'), ('1236', 'Gerry'), ('1237', 'Grey'), ('1237', 'Zed')

SQL Fiddle
table1

id name
1 John
1 Merry
2 Ted
3 Jack
3 Zed

table2

id name
1234 John
1234 Peter
1236 Gerry
1237 Grey
1237 Zed

我想要這兩張表合起來,並且將同時出現在 table1 與 table2 名稱所對應 id 歸成同一類

舉例來說 table1 有 John 對應 id 1, table2 有 John 對應 id 1234,因此 table1 的 id 1 與 table2 的 id 1234 歸在同一類,連帶 Merry、Peter 都歸在同一類

id name c
1 John 1
1 Merry 1
2 Ted 2
3 Jack 3
3 Zed 3
1234 John 1
1234 Peter 1
1236 Gerry 1236
1237 Grey 3
1237 Zed 3
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

2
rogeryao
iT邦超人 8 級 ‧ 2021-07-05 12:31:47
最佳解答
CREATE TABLE table1 (
id varchar(10),
name varchar(10));
  
INSERT INTO table1 VALUES ('1', 'John'), ('1', 'Merry'), ('2', 'Ted'), ('3', 'Jack'), ('3', 'Zed');
CREATE TABLE table2 (
id varchar(10),
name varchar(10));
  
INSERT INTO table2 VALUES ('1234', 'John'), ('1234', 'Peter'), ('1236', 'Gerry'), ('1237', 'Grey'), ('1237', 'Zed');
SELECT CAST(A.id AS INT) AS id,A.name AS name, A.id AS c
FROM table1 AS A
UNION 
SELECT CAST(E.id AS INT) AS id,E.name AS name,ISNULL(F.c,E.id) AS c
FROM table2 E
LEFT JOIN (
SELECT D.id,D.name, B.id AS c
FROM table1 AS B
INNER JOIN table2 AS D ON D.name=B.name
) AS F ON F.id=E.id
ORDER BY id,name

Demo

wrxue iT邦好手 1 級 ‧ 2021-07-05 13:15:20 檢舉

太厲害啦!/images/emoticon/emoticon35.gif

0
japhenchen
iT邦超人 1 級 ‧ 2021-07-05 13:38:35

比較直覺的方法,用view

(左邊create table那格不動,動右邊那格)

create view vw_myview
as 
  select * from table1
  union 
  select * from table2  
go



SELECT * FROM vw_myview order by cast(id as int)

改一下,這樣比較好

create view vw_myview
as 
  select *,1 as subid from table1
  union 
  select *,2 as subid from table2  
go


SELECT * FROM vw_myview order by subid

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE table1 (
  id varchar(10),
  name varchar(10)
  )
  
  CREATE TABLE table2 (
  id varchar(10),
  name varchar(10)
  )
  
  INSERT INTO table1 VALUES ('1', 'John'), ('1', 'Merry'), ('2', 'Ted'), ('3', 'Jack'), ('3', 'Zed');
  INSERT INTO table2 VALUES ('1234', 'John'), ('1234', 'Peter'), ('1236', 'Gerry'), ('1237', 'Grey'), ('1237', 'Zed')

Query 1:

create view vw_myview
as 
  select *,1 as subid from table1
  union 
  select *,2 as subid from table2  

Results:

Query 2:

SELECT * FROM vw_myview order by subid

Results:

|   id |  name | subid |
|------|-------|-------|
|    1 |  John |     1 |
|    1 | Merry |     1 |
|    2 |   Ted |     1 |
|    3 |  Jack |     1 |
|    3 |   Zed |     1 |
| 1234 |  John |     2 |
| 1234 | Peter |     2 |
| 1236 | Gerry |     2 |
| 1237 |  Grey |     2 |
| 1237 |   Zed |     2 |
wrxue iT邦好手 1 級 ‧ 2021-07-05 16:48:00 檢舉

大大您好 ~ 雖然您的解方與我想要的不同,還是感激您的回覆!

我要發表回答

立即登入回答