从Drupal 7迁移博客数据，合并两个select语句并创建输出表

Question

我正在将一个门户从drupal 7迁移到新的堆栈。我想从节点表和url_alias中检索和组合数据，使blog和slugs在同一个表中。

我能够将博客数据获取到一个语句中，并与第二个语句获取slugs(使用替换函数和case)。我如何结合这两个语句并将结果插入到新表中?

声明1:

Select t1.nid, 
t1.vid, 
t1.title, 
t2.body_value as body 
from `node` as t1 
left join `field_revision_body` as t2 ON t1.vid = t2.revision_id
where t1.type = 'blog';

输出如下:

|nid|vid|title|body|

声明2:

SELECT CASE WHEN url_alias.alias LIKE "%news/%" THEN REPLACE(url_alias.alias, "news/", "") end as slug, 
case when url_alias.source LIKE "%node/%" THEN 
REPLACE(url_alias.source, "node/", "") end as nodeId 
from url_alias 
where url_alias.alias LIKE "news/%" and url_alias.source like "%node/%";

输出如下:

|slug|nodeId|

删除"node/"从url_alias。源我现在有nodeId，这是一个关键的nid或vid。我想创建一个输出表，它将两个输出合并到一个新表中，如:

标题| |国家免疫日vid | | |身体蛞蝓|

我如何加入他们?

Answer 1

一种方法是使用UNION。

你可以尝试这样做:

INSERT INTO your_table_name (   nid 
,vid 
,title 
,body 
,slug 
,nodeId

SELECT       a.nid 
,a.vid 
,a.title 
,a.body 
,a.slug 
,a.nodeId


FROM (   
SELECT * FROM (
SELECT t1.nid, 
t1.vid, 
t1.title, 
t2.body_value as body,
null as slug,
null as nodeId,
FROM `node` as t1 
LEFT JOIN  `field_revision_body` as t2 ON t1.vid = t2.revision_id
WHERE t1.type = 'blog' 
) 
UNION

(
SELECT null as a.nid, 
null as vid, 
null as title, 
null as body 
CASE WHEN url_alias.alias LIKE "%news/%" THEN REPLACE(url_alias.alias, "news/", "") end as slug, 
CASE WHEN url_alias.source LIKE "%node/%" THEN  REPLACE(url_alias.source, "node/", "") end as nodeId 
FROM url_alias 
WHERE url_alias.alias LIKE "news/%" AND url_alias.source LIKE "%node/%"
)   

) as a;