MySQL 如何将两个表(不同的字段)与两个语句组合在一起

Question

我想合并两个具有不同列的表。 原因是我使用的报表生成器 (http://wpreportbuilder.com/( 仅支持一个数据库源和一个.docx模板。

第一张表：

CLIENT    DATE             TITLE      NAME      SOLUTION
 ABC      2019-03-01     title123   compA       fix1234
 ABC2     2019-03-01     title124   compB       fix12345

第二个表(可以有更多列(：

CLIENT      DATE          CUSTOMER    REPORT       PHONE
 ABC3     2019-03-01         abc       PDF         1234

期望的输出

CLIENT        DATE      TITLE     NAME    SOLUTION   CUSTOMER   REPORT    PHONE
 ABC      2019-03-01  title123   compA   fix1234      
 ABC2     2019-03-01  title123   compB   fix12345
 ABC3     2019-03-01                                  abc        PDF     1234

这样，我可以轻松地将我的.docx模板与 {title.all} 、{name.all}、{report.all} 一起使用，并自动从数据库中导入值并将它们插入回 word 文档中。

如果列数

匹配，我可以轻松做到这一点，但如果列数不匹配(如下面的代码(，它会返回"不匹配错误"。

我的代码：

SELECT 
 client AS Client,
 date as "Date Scanned",
 scanner AS Scanner,
 risk AS Risk, 
 host AS Host, 
 name AS Title, 
 solution AS Solution
FROM sss1_latest
WHERE risk regexp "High" 

UNION
SELECT 
client AS Client,
date as "Date Scanned",
Recommendation AS Solution
FROM sss2_latest
WHERE Recommendation regexp "fix1" 

上面的

代码并不反映我上面的第一个/第二个表。 这只是为了解释目的。

Answer 1

UNION中的所有查询都必须选择相同数量的列。您可以选择NULL或''作为仅存在于其他表中的列的占位符。

SELECT 
 client AS Client,
 date as "Date Scanned",
 scanner AS Scanner,
 risk AS Risk, 
 host AS Host, 
 name AS Title, 
 solution AS Solution,
 '' AS Customer,
 '' AS Report,
 '' AS Phone
FROM sss1_latest
WHERE risk regexp "High" 

UNION
SELECT 
 client AS Client,
 date as "Date Scanned",
 '' AS Scanner,
 '' AS Risk, 
 '' AS Host, 
 '' AS Title, 
 '' AS Solution,
 Customer,
 Report,
 Phone
FROM sss2_latest
WHERE Recommendation regexp "fix1"