我有两张表:
connections
id | publisherId | authorId和
books
id | connectionId | title我想合并这些表,只得到一个表:
books
id| publisherId | authorId | title我如何只用一个SQL查询就能做到这一点?
发布于 2012-05-10 10:14:51
CREATE TABLE newtable
SELECT b.id, c.publisherId, c.authorID, b.title
FROM books b
INNER JOIN connections c
on c.id = b.connectionId未经测试,但这应该可以做到。我假设您想要books表中的ID,否则您需要c.id而不是b.id。
然后您可以删除旧的表,并将其重命名为您想要的任何名称。
发布于 2012-05-10 10:21:46
CREATE TABLE connections_books
SELECT books.id as id,
connections.publisherId as publisherId,
connections.authorId as authorId,
books.title as title
FROM books join connections on books.connectionId = connections.id;首先使用select部件测试查询:
SELECT books.id as id,
connections.publisherId as publisherId,
connections.authorId as authorId,
books.title as title
FROM books join connections on books.connectionId = connections.id;一旦这为您提供了所需的内容,请继续创建该表。
重要的是要确保新表中的列名是正确的。使用select语句测试来确保您的列名是您想要的。如果要更改列名称,请更改每个选定列的as ...名称。
https://stackoverflow.com/questions/10526656
复制相似问题