如何连接两个具有相同属性的表？

ddrv8njm 于 2021-06-17 发布在 Mysql

关注(0)|答案(3)|浏览(371)

我有两个表，我想用两个表中的数据创建一个具有相同属性的新表。下面是我正在尝试的代码

/* Create a table called NAMES */
CREATE TABLE NAMES(Id integer PRIMARY KEY, Name varchar(100));
CREATE TABLE GAMES(Id integer PRIMARY KEY, Name varchar(100));
CREATE TABLE JJ(Id integer PRIMARY KEY, Name varchar(100));

/* Create few records in this table */
INSERT INTO NAMES VALUES(1,'Tom');
INSERT INTO NAMES VALUES(2,'Lucy');
INSERT INTO NAMES VALUES(3,'Frank');
INSERT INTO NAMES VALUES(4,'Jane');
INSERT INTO NAMES VALUES(5,'Robert');
INSERT INTO GAMES VALUES(7,'Football');
INSERT INTO GAMES VALUES(6,'Rugby');
COMMIT;

/* Display all the records from the table */
SELECT * FROM NAMES;
SELECT * FROM GAMES;
INSERT INTO JJ (Id, Name) VALUES((SELECT * FROM NAMES), (SELECT * FROM GAMES));

SELECT * FROM JJ; 'Error: near line 21: sub-select returns 2 columns - expected 1'

sql mysql Database

来源：https://stackoverflow.com/questions/53736631/how-to-join-two-tables-with-same-attributes

3条答案

按热度按时间

v6ylcynt1#

我建议宣布 JJ 作为：

CREATE TABLE JJ (
    Id integer auto_increment PRIMARY KEY,
    Name varchar(100)
);

insert into jj (name)
    select name from names union all
    select game from games;

赞(0）回复(0）举报 2021-06-18

56lgkhnf2#

您尝试合并两个表的方式不太好，因为在b/c中，两个不同的表主键可能相同，这将在第三次插入表时引发错误（重复键）。我更喜欢改变你的表结构，使所有三个表主键自动递增，只按名称而不是id

INSERT INTO JJ ( Name)
select name from (
SELECT name FROM NAMES
union all
SELECT name FROM GAMES
) t

赞(0）回复(0）举报 2021-06-18

kiz8lqtg3#

这将适用于oracle：

INSERT INTO JJ (Id, Name)((SELECT * FROM NAMES) union all(SELECT * FROM GAMES));

对于mysql：

insert into JJ select * from Names union all select * from Games;

但正如zaynul所说，当两个主键匹配时，它将抛出一个错误！！！！

赞(0）回复(0）举报 2021-06-17

我来回答

如何连接两个具有相同属性的表？

3条答案

相关问题

热门标签

最新问答