如何组合具有一些列相同和不同的2个表的行 [英] How to combine rows of 2 tables having some columns same and some different

问题描述

我有两张用于用户帖子的表格，一个用于文本帖子，另一个用于多媒体帖子，两个表格的结构如下。

table text_post

  + -------- + ------------ -  + ----------- + ----------- + ----------------- + ----- ------ + 
 | postid | post_content | post_user | post_type | post_visibility | post_date | 
 + -------- + -------------- + ----------- + --------- -  + ----------------- + ----------- + 
  

表 multimedia_post

  + ---- --- + ------------ + -------- + --------- + --------- + ---- ----------- + --------- + 
 | post_id | post_content | post_url | post_user | post_type | post_visibility | post_date | 
 + ------- + ------------ + -------- + --------- + ----- ---- + --------------- + --------- + 
  

$ text_post中的
$ b

post_content 是由用户和multimedia_post发布的文本数据 post_content 是与多媒体文件相关联的字幕文本，因此基本上 post_content 是文本内容。所有的列是常见的，只有在多媒体文件夹中的post_url不同，我想结合这两个表的结果如下：

  +  - ------ + ------------ + -------- + --------- + --------- + -------------- + --------- + 
 | post_id | post_content | post_url | post_user | post_type | post_visibility | post_date | 
 + ------- + ------------ + -------- + --------- + ----- ---- + --------------- + --------- + 
 | 1 |一些文字| null | 1 | < type> | < visibility> | < date> | 
 + ------- + ------------ + -------- + --------- + ----- ---- + --------------- + --------- + 
 | 1 | img_caption | < url> | 1 | < type> | < visibility> | < date> | 
 + ------- + ------------ + -------- + --------- + ----- ---- + --------------- + --------- + 
 ..等等
  

这里返回的第一行来自 text_post 所以 post_url 被设置为null，因为只有 multimedia_post 具有该列...而第二行来自 multimedia_post as有一个url的帖子...

如何实现这个？

解决方案

您可以从两个 SELECT s 中 UNION ALL s，返回列为排队的列：

  SELECT 
 postid as post_id 
，post_content 
，null as post_url 
，post_user 
 ...  - 其余text_post列包含在select 
 FROM text_post 
 UNION ALL 
 SELECT 
 post_id 
，img_caption as post_content 
，post_url 
，post_user 
 ...  - 选择
 FROM multimedia_post 
中的其他multimedia_post列  text_post  null作为post_url 之后的
 
 >：这是必要的，以排列两个select语句的列。
 
I have two tables for user posts, one for text post and one for multimedia post, the structure of both tables is as follows.

table text_post
+--------+--------------+-----------+-----------+-----------------+-----------+
| postid | post_content | post_user | post_type | post_visibility | post_date |
+--------+--------------+-----------+-----------+-----------------+-----------+
table multimedia_post
+-------+------------+--------+---------+---------+---------------+---------+
|post_id|post_content|post_url|post_user|post_type|post_visibility|post_date|
+-------+------------+--------+---------+---------+---------------+---------+
in text_post post_content is text data that posted by user and in multimedia_post post_content is caption text that is associated with multimedia file, so basically post_content is text matter. all columns are common, only the post_url is different in multimedia_post, I want to combine result of these two table as follows
+-------+------------+--------+---------+---------+---------------+---------+
|post_id|post_content|post_url|post_user|post_type|post_visibility|post_date|
+-------+------------+--------+---------+---------+---------------+---------+
|  1    |  some text | null   | 1       | <type>  | <visibility>  | <date>  |
+-------+------------+--------+---------+---------+---------------+---------+
|  1    | img_caption| <url>  | 1       | <type>  | <visibility>  | <date>  |
+-------+------------+--------+---------+---------+---------------+---------+
..and so on
here 1st row returned is from text_post so post_url is set to null because only multimedia_post has that column...and second row is from multimedia_post as there is url of the post...

How can I achieve this?
解决方案
You can UNION ALL from two SELECTs that return columns that "line up", like this:
SELECT
    postid as post_id
,   post_content
,   null as post_url
,   post_user
... -- the rest of text_post columns included in the select
FROM text_post
UNION ALL
SELECT
    post_id
,   img_caption as post_content
,   post_url
,   post_user
... -- the rest of multimedia_post columns included in the select
FROM multimedia_post
Note how null as post_url is selected from text_post: this is necessary in order to line up the columns of the two select statements.

                        
这篇关于如何组合具有一些列相同和不同的2个表的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！