MySQL-如何创建一个新表，该表是两个现有表的主键上的联接 [英] MySQL - How to create a new table that is a join on primary key of two existing tables

问题描述

我有两个现有表，除了主ID(varchar，不是int)外，具有不同的字段.我想创建第三个表，该表本质上是这两个表的合并，这样对于一个给定的主键，我将所有字段都放在一个表中.

I have two existing tables, with different fields, except for Primary ID (a varchar, not an int). I want to create a third table which is essentially a merge of these two, such that for a given Primary Key I have all fields in one table.

做这件事的bext方法是什么?

What's the bext way of doing this?

非常感谢

推荐答案

如果您确定对于给定的主ID，两个表中只有一行并且恰好有一行，那么这应该可行:

If you are sure you have one and exactly one row in both tables for a given primary ID, then this should work:

SELECT
    tablea.field1, tablea.field2, tablea.field3, ... tablea.fieldn, <---- field list
    tableb.field1, tableb.field2, tableb.field3, ... tableb.fieldm  <---- field list
FROM
    tablea, tableb
WHERE
    tablea.primaryID = tableb.primaryID

如果实际上不需要它们，则可能想从字段列表中省略tablea和tableb的主ID字段(在此查询中，由于tablea.primaryID = tableb.primaryID条件，两者将包含相同的值).

You might want to omit tablea's and tableb's primary ID field from the field list if you do not actually need them (in this query both will contain the same value due to the tablea.primaryID = tableb.primaryID condition).

VIEW的语法也相对相似.

这篇关于MySQL-如何创建一个新表，该表是两个现有表的主键上的联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！