将2个表与相同的列合并 [英] Combine 2 tables with identical columns

问题描述

已经遍及stackoverflow和Google，并且相距很近，但是还没有找到完整的解决方案...也许这是一个简单的解决方案，所以以前不需要有人寻求帮助吗?

Have looked all over stackoverflow and Google and have come close, but haven't found a complete solution yet...maybe it's a simple solution no one's needed to ask for help before?

我有2门课程&与课程相关的信息(Master_Table& Import_Table). Master_Table表包含每个课程的核心内容/信息，但缺少日期&这些时间每周更改一次(目前大约有240行核心课程).请参见下面的Master_Table结构:

I have 2 tables of courses & course related info (Master_Table & Import_Table). Master_Table table contains the core content/info on each course but is missing dates & times as these change weekly (there are currently about 240 rows of core courses). See Master_Table structure below:

主表

+-----------+----------------+------------+------------+-------------------+------+
| sku_crmid |  post_title    | start_date | start_time |   post_content    | u_id |
+-----------+----------------+------------+------------+-------------------+------+
| skuid1    | Unique Title 1 |    null    |    null    | Unique Content 1  | null |
| skuid2    | Unique Title 2 |    null    |    null    | Unique Content 2  | null |
| skuid2    | Unique Title 3 |    null    |    null    | Unique Content 3  | null |
+-----------+----------------+------------+------------+-------------------+------+

Import_Table每周更新一次，并带有新的日期&次(每次导入最多可以有2000行以上).它还包含post_tile ... post_title是两个表之间唯一的 ONLY 事物.虽然post_title将在Master_Table中显示一次，但它将在Import_Table中显示几次(每个新日期和时间一行).请参见下面的Import_Table结构:

Import_Table is updated weekly with new dates & times (could be as many as 2000+ rows/import). It also contains post_tile...the post_title is the ONLY thing unique between the 2 tables. While a post_title will show once in Master_Table, it will show several times in Import_Table (one row for each new date & time). See Import_Table structure below:

导入表

+-----------+----------------+------------+------------+-------------------+------+
| sku_crmid |  post_title    | start_date | start_time |   post_content    | u_id |
+-----------+----------------+------------+------------+-------------------+------+
|   null    | Unique Title 1 | 12/02/2013 |   8:00am   |     null          | null |
|   null    | Unique Title 1 | 12/16/2013 |   8:00am   |     null          | null |
|   null    | Unique Title 2 | 12/09/2013 |   8:00am   |     null          | null |
|   null    | Unique Title 2 | 12/16/2013 |   8:00am   |     null          | null |
|   null    | Unique Title 3 | 12/02/2013 |   8:00am   |     null          | null |
|   null    | Unique Title 3 | 12/09/2013 |   8:00am   |     null          | null |
+-----------+----------------+------------+------------+-------------------+------+

我需要能够将Master_Table和Import_Table合并为一个表/输出，然后可以将其导出为CSV.如果Import_Table中有一个"null"，而Master_Table中有一个数据，则需要在输出中将该"null"替换为数据.如果同一列的两个表中都存在"null"，则"null"输出就可以.

I need to be able to combine both Master_Table and Import_Table into one table/output that I can export to CSV. If there is a "null" in the Import_Table and data in the Master_Table, I need that "null" replaced with data in the output. If "null" exists in both tables in the same column, then "null" out is OK.

下面是2个可能的输出，如果可能的话，它们将是确定的.选项1可以并且可以使用，但选项2更理想.我已经看到选项2可以通过在查询中插入'，'来完成，但是管道是我所需要的，在'|'的两边都没有空格.

Below are 2 possible outputs that would be OK if they are even possible. Option 1 is OK and would work, but Option 2 is more ideal. I have seen option 2 done with inserting a ', ' in a query before, but a pipe is what I would need with no space on either side of the '|'...

需要输出-选项1

+-----------+----------------+------------+------------+-------------------+------+
| sku_crmid |  post_title    | start_date | start_time |   post_content    | u_id |
+-----------+----------------+------------+------------+-------------------+------+
|   null    | Unique Title 1 | 12/02/2013 |   8:00am   |  Unique Content 1 | id_1 |
|   null    | Unique Title 1 | 12/16/2013 |   8:00am   |  Unique Content 1 | id_2 |
|   null    | Unique Title 2 | 12/02/2013 |   8:00am   |  Unique Content 2 | id_3 |
|   null    | Unique Title 2 | 12/16/2013 |   8:00am   |  Unique Content 2 | id_4 |
|   null    | Unique Title 3 | 12/02/2013 |   8:00am   |  Unique Content 3 | id_5 |
|   null    | Unique Title 3 | 12/09/2013 |   8:00am   |  Unique Content 3 | id_6 |
+-----------+----------------+------------+------------+-------------------+------+

需要的输出-选项2(最佳解决方案)

+-----------+----------------+-------------------------------------+-------------------+------+
| sku_crmid |  post_title    |          start_date_time            |   post_content    | u_id |
+-----------+----------------+-------------------------------------+-------------------+------+
|   skuid1  | Unique Title 1 | 12/02/2013 8:00am|12/16/2013 8:00am | Unique Content 1 | id_1 |
|   skuid2  | Unique Title 2 | 12/09/2013 8:00am|12/16/2013 8:00am | Unique Content 2  | id_2 |
|   skuid3  | Unique Title 3 | 12/02/2013 8:00am|12/09/2013 8:00am | Unique Content 3  | id_3 |
+-----------+----------------+-------------------------------------+-------------------+------+

因此，最后，我需要一种方法来执行以下操作(如果可能):

1. 将两个表合并为一个并分配一个unique_id，以便我可以将其导出为CSV

1. Combine both tables into one and assign a unique_id so I can export as a CSV

以一种永不重复的方式分配一个unique_id生成

Assign a unique_id generation in a way that will never repeat

组合所有开始日期和时间； start_times移至一个行/单元格，并用管道(|)分隔它们，并且管道两边都没有空格

Combine all start_dates & start_times to one line/cell and separate them with a pipe (|) and no spaces on either side of the pipe

如果不是管道解决方案...上面的选项1也可以...

If the pipe solution isn't an option...option 1 above is OK too...

如果有帮助，我正在使用MySQL

If it helps I am using MySQL

推荐答案

您可以使用uuid()函数分配唯一键.另外，您必须使用变量，如以下示例所示.

You can assign a unique key using the uuid() function. Alternatively, you have to use variables, as in the following example.

查询的关键是将主表连接到输入表，然后将行汇总到一行.

The key to the query is joining the master table to the input table and then aggregating the rows down to one row.

您想要的查询类似于:

select coalesce(it.sku_crmid, mt. sku_crmid) as sku_crmid,
       mt.post_title,
       group_concat(concat(it.start_date, it.start_time) separator '|') as start_date_time,
       coalesce(it.post_content, mt.post_content) as post_content,
       @rownum := @rownum + 1 as uid
from Master_table mt left outer join
     Import_Table it
     on it. post_title = mt. post_title cross join
     (select @rownum := 0) const
group by post_title;

由于日期/时间格式的问题，我说类似".您可能想要以其他方式设置这些列的格式.

I say "something like" because of the issue of date/time formats. You might want to format those columns differently.

这篇关于将2个表与相同的列合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！