mysql - 将多个表连接到单个 mysql 表中，为每条记录添加新字段

Question

我有一个关于 mysql 的问题。我有多个表(德累斯顿、杜塞尔多夫、英格兰等)，每个表都有以下字段(名称、登录名、mac地址)。有些用户位于多个表中(同一用户可以位于两个或多个表中。)我想创建一个可以看到所有用户的表。我想知道是否可以在每个原始文件的末尾添加一个字段，告诉它来自哪里并合并所有重复的文件。我希望我说清楚了......我对 mysql 真的很陌生......

致以诚挚的问候...

这是我的表格:

杜塞尔多夫:

--------------------------------
|Username | value | macaddress |
-------------------------------
|jose     | 10    | 00:00:00:00|
--------------------------------
|maria    | 20    | 00:00:00:01|
--------------------------------

德累斯顿:

--------------------------------
|Username | value | macaddress |
--------------------------------
|jose     | 10    | 00:00:00:00|
--------------------------------
|maria    | 20    | 00:00:00:01|
--------------------------------
|juan     | 15    | 00:00:00:02|
--------------------------------

英格兰:

--------------------------------
|Username | value | macaddress |
-------------------------------
|jose     | 10    | 00:00:00:00|
-------------------------------
|maria    | 20    | 00:00:00:01|
--------------------------------
|pedro    | 20    | 00:00:00:03|
--------------------------------

一起:

-----------------------------------------------------------------
|Username | value | macaddress | Dusseldorf | Dresden | England |
-----------------------------------------------------------------
|jose     | 10    | 00:00:00:00|     X      |    X    |    X    |
-----------------------------------------------------------------
|maria    | 20    | 00:00:00:01|     X      |    X    |    X    |
-----------------------------------------------------------------
|juan     | 15    | 00:00:00:02|            |    X    |         |
-----------------------------------------------------------------
|pedro    | 20    | 00:00:00:03|            |         |    X    |
-----------------------------------------------------------------

Answer 1

您可以使用多个 UNION 语句选择此数据，并使用 CREATE TABLE 创建新表:

CREATE TABLE all_merged AS
 SELECT s.*,
        concat((SELECT 'Dresden' 
                  FROM Dresden
                 WHERE Dresden.name=s.name
                   AND Dresden.loginname = s.loginname
                   AND Dresden.macaddress = s.macaddress
               ),
               (SELECT 'Dusseldorf' 
                  FROM Dusseldorf
                 WHERE Dresden.name=s.name
                   AND Dresden.loginname = s.loginname
                   AND Dresden.macaddress = s.macaddress
               ),
               ...) as `location`
   FROM
        ( SELECT name, loginname,macaddress
            FROM Dresden
           UNION
          SELECT name, loginname,macaddress
            FROM Dusseldorf
           UNION
           ....
        ) AS S
 )

虽然我认为构建 View 可能更适合您的情况:

CREATE VIEW v_all_merged AS
 SELECT s.*,
        concat((SELECT 'Dresden' 
                  FROM Dresden
                 WHERE Dresden.name=s.name
                   AND Dresden.loginname = s.loginname
                   AND Dresden.macaddress = s.macaddress
               ),
               (SELECT 'Dusseldorf' 
                  FROM Dusseldorf
                 WHERE Dresden.name=s.name
                   AND Dresden.loginname = s.loginname
                   AND Dresden.macaddress = s.macaddress
               ),
               ...) as `location`
   FROM
        ( SELECT name, loginname,macaddress
            FROM Dresden
           UNION
          SELECT name, loginname,macaddress
            FROM Dusseldorf
           UNION
           ....
        ) AS S
 )

我刚刚创建了一个 sqlfiddle显示解决方案