将逗号分隔列拆分为多对多关系的 SQL 查询

Question

我得到了一个 3Gb 的 csv 文件，我需要将其导入到 SQL Server 2012 中。

我现在有 500 万行数据在一个看起来像这样(简化)的暂存表中。

暂存表:

+-------------------+------------+---------------+------------+
|       Name        | Thumbnail  |     Tags      | Categories |
+-------------------+------------+---------------+------------+
| History           | thumb1.jpg | history,essay | history    |
| Nutricion Lecture | thumb2.jpg | food,essay    | health     |
+-------------------+------------+---------------+------------+

问题是关于我的暂存表中的 tags 和 categories 列。

我怎样才能将信息从暂存表转移到我的实际表并为每个标签和类别创建一个唯一记录 -- 和创建所需的许多-对多关系？

它需要根据现有标签检查每个标签，以创建新记录 - 或 - 获取现有标签的 Id。

程序:

+----+-----------+------------+
| id |  Program  | Thumbnail  |
+----+-----------+------------+
|  1 | History   | thumb1.jpg |
|  2 | Nutricion | thumb2.jpg |
+----+-----------+------------+

标签:

+----+---------+
| Id |   Tag   |
+----+---------+
|  1 | history |
|  2 | essay   |
|  3 | food    |
+----+---------+

(省略了类别表，因为它看起来与标签相同)

多对多关系:

Programs_Tags:

+---------+-----+
| program | tag |
+---------+-----+
|       1 |   1 |
|       1 |   2 |
|       2 |   2 |
+---------+-----+

程序类别:

+---------+----------+
| program | category |
+---------+----------+
|       1 |        1 |
|       2 |        2 |
+---------+----------+

我认为这在纯 SQL 中比为它编写一个工具要快。

Answer 1

我不确定这在 SQL 中是否更快。但是，这是一种方法。

首先，创建您需要的五个表:

• 程序
• 标签
• 类别
• 程序标签
• 项目类别

具有适当的结构，包括身份 ID 列。

然后将数据加载到程序中。这个很简单，选择合适即可。

然后创建Tags 和Categories 表。以下是加载 Tags 表的方法:

with cte as (
      select (case when tags like '%,%'
                   then left(tags, charindex(tags, ','))
                   else tags
              end) as tag,
             (case when tags like '%,%'
                   then substring(tags, charindex(tags, ',') + 1, len(tags))
              end) as resttags
      from staging
      where tags is not null and tags <> ''
      union all
      select (case when resttags like '%,%' then left(resttags, charindex(tags, ','))
                   else resttags
              end) as tag,
             (case when tags like '%,%'
                   then substring(resttags, charindex(resttags, ',') + 1, len(testtags))
              end) as resttags
      from cte
      where resttags is not NULL and resttags <> ''
     )
select distinct tags
from cte;

(显然这需要一个insert)。

对类别做同样的事情。

然后使用以下方式加载 ProgramTags:

select p.ProgramId, t.TagId
from staging s join
     Programs p
     on s.<whatever> = p.<whatever> join
     Tags t
     on ','+s.tags+',' like '%,'+t.tag+',%';

第一个join是获取program id。第二个是获取适当的标签。性能不会很好，但可能足以满足您的需要。