[SQL] 該怎麼歸成一類

sql 歸類

wrxue 2021-07-05 11:44:15 ‧ 1218 瀏覽

分享至

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 }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 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

回應 1
分享
檢舉

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

太厲害啦！

登入發表回應

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)

回應 2
分享
檢舉

japhenchen iT邦超人 1 級 ‧ 2021-07-05 13:41:32 檢舉

改一下，這樣比較好

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 |