时间戳

首页 > 解决方案 > 从 Drupal 7 迁移博客数据，结合两个 select 语句并创建输出表

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

问题描述

我正在将门户从 drupal 7 迁移到新堆栈。我想从节点表和 url_alias 中检索和组合数据，以便在同一个表中拥有博客和 slug。

我能够将博客数据提取到一个语句中，并使用第二个语句获取 slugs（使用替换函数和大小写）。如何将这两个语句组合起来并将结果插入到新表中？

声明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|

从 url_alias.source 中删除“node/”后，我现在有了 nodeId，它是 nid 或 vid 的键。我想创建一个输出表，它将两个输出组合到一个新表中，例如：

|nid|vid|标题|正文|蛞蝓|

我怎样才能加入他们？

标签： mysqldatabasedrupal-7

解决方案

一种方法是使用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;

推荐阅读