SQL用于解析多行数据？ [英] SQL for parsing multi-line data?

问题描述

我不必从Excel中导入数据到数据库定期不幸任务。该表看起来是这样的：

I have the unfortunate task of having to import data from excel into a database on a regular basis. The table looks something like this:

  IssueID   References  
  1234      DocID1<cr>DocID2<cr>DocID3
  1235      DocID1
  1236      DocID2
  1237      DocID2<cr>DocID3

引用是多行文本字段。我想要做的就是创建一个文档表一对许多关系到发行表，而不是让这些多行引用。

References is a multi-line text field. What I'm trying to do is create a Docs table with one-to-many relationship to the Issue table, rather than having these multi-line references.

我有以下表中定义的：

问题：IssueKey，IssueID，IssueFields

Issue: IssueKey, IssueID, IssueFields

文档：DocKey，DOCID，DocRev，DocOwner等

Doc: DocKey, DocID, DocRev, DocOwner, etc

文档链接：LinkKey，DocKey，IssueKey

DocLink: LinkKey, DocKey, IssueKey

由于这将被重复运行中，文档表将已经与所定义的DocIDs存在。所以，我想要做的是为每个DOCID在引用列的查询或VBA code搜索和添加基于IssueID如果一个不存在的链接。

Since this will be run repeatedly, the Doc table will already exist with the DocIDs defined. So, what I want to do is have a query or VBA code search for each DocID in the References column and add a link based on IssueID if one does not already exist.

简单吧？

杰夫

澄清：

1）我有一个第三列名为VAL1，显示还有其他列，但似乎混淆问题。其实有很多（的方式很多，最容易被忽视）源表中的列，但我只关心上述两种。

1) I had a third column called "Val1" to show that there were other columns, but that seemed to confuse the issue. There are actually many (way to many, most ignored) columns in the source table, but I only care about the two above.

2）我没有解析为一个分隔符或任何过于偏执：参考文献包含一个或多个唯一定义文档参考号码（如文本形式存储）。所以，LIKE过滤器将调高对案件IssueIDs的名单逐个。

2) I don't have to parse for a delimiter or anything too paranoid: References contains one or more uniquely defined document reference numbers (stored as text). So, a LIKE filter will turn up the list of IssueIDs on a case by case basis.

3）在这里是可接受的输出的一个示例：

3) Here is an example of acceptable output:

IssueID   References
1234      DocID1
1234      DocID2
1234      DocID3
1235      DocID1
1236      DocID2
1237      DocID2
1237      DocID3

的理想解决方案将采取原始的excel表（顶部）和这两个表

The ideal solution would take the original excel table (top) and these two tables:

IssueKey   IssueID
   1        1234
   2        1235
   3        1236
   4        1237

DocKey     DocID
  1        DocID1
  2        DocID2
  3        DocID3

和填充/更新链接表：

LinkKey  IssueKey  DocKey
   1        1        1
   2        1        2
   3        1        3
   4        2        1
   5        3        2
   6        3        3

4）这是我所期待的解决方案的例子（创建＃3段）。不幸的是它崩溃的访问，所以我不能告诉你的语法是正确的（编辑，以反映字段名以上）。

4) Here is an example of what I expected for a solution (creates #3 above). Unfortunately it crashes Access, so I can't tell if the syntax is correct (edited to reflect field names above).

SELECT Q1.IssueID, D1.DocID
FROM Docs AS D1, Issues AS Q1
WHERE Q1.IssueID IN 
   ((SELECT Q2.IssueID from Issues AS Q2 where (Q2.References) Like D1.DocID));

5）放弃对的时刻访问，我有以下MySQL中工作：

5) Giving up on Access for the moment, I've got the following working in MySQL:

SELECT Q1.IssueID, D1.DocID
FROM Docs AS D1, Issues AS Q1
WHERE Q1.IssueID IN 
   ((SELECT Q2.IssueID from Issues AS Q2 where (Q2.References) Like '%DocID1%'));

这个工程，我期望 - 我让每一个IssueID一个参考DocID1，重复表中的每一文档。通过上述数据，将是这样的：

This works as I'd expect - I get every IssueID with a Reference to DocID1, repeated for every Doc in the table. With the above data it would look like:

IssueID   References
1234      DocID1
1234      DocID2
1234      DocID3
1235      DocID1
1235      DocID2
1235      DocID3

现在我只想用'％'+ D1.DocID +'％'代替'％DocID1％ - 限制的结果，这些文档ID里面居然有一个匹配。出于某种原因，我得到零记录，当我做到这一点 - 我想我有语法将通配符在相关领域的错

Now I just want to replace the '%DocID1%' with '%'+D1.DocID+'%' - limiting the results to those document IDs which actually have a match. For some reason I'm getting zero records when I do this - I think I have the syntax for putting wildcards on the correlated field wrong.

6）以下工作在MySQL提供＃3以上，但翻译成访问相同的查询崩溃的：

6) The following works to provide #3 above in MySQL, but the same query translated to access crashes it:

SELECT Q1.IssueID, D1.DocID
FROM Docs AS D1, Issues AS Q1
WHERE Q1.IssueID IN 
   ((SELECT Q2.IssueID from Issues AS Q2 where (Q2.References) Like        
        CONCAT('%',D1.DocID,'%')));

[在获得它变成（'和; D1.DocID和放大器; 的'）

结论：访问吮吸

推荐答案

我觉得用在标题中注明解析混淆了废话了大家。在Access中的错误是，在查询（而不是表）进行相关查询导致挂起。所以，相反，我认为广告引用列（与多行文字）的问题表，使我有机会到其他领域创建的临时表。最终的查询创建如上所述的链接表中，伴随着的docID和IssueID供参考：

I think using the word "parse" in the title has confused the crap out of everyone. The bug in Access was that a correlated query performed on a query (instead of a table) causes a hang. So instead, I created a temporary table that ads the References column (with the multi-line text) to the Issues table so I have access to the other fields. The final query creates the link table described above, along with the DocID and IssueID for reference:

SELECT Q1.IssueID, Q1.IssueKey, D1.DocKey, D1.DocID
FROM Issues AS Q1, Documents AS D1
WHERE Q1.IssueID in 
  (SELECT  Q2.IssueID FROM Issues AS Q2 WHERE Q2.References LIKE ("*" & D1.DocID & "*"));

内选择拉的有在列引用给定文档的问题清单。对于每个文档外部选择执行这一点，从而在聚合列表

The inner select pulls the list of issues which has a given document in the references column. The outer select performs this for each document, resulting in the aggregate list.

这篇关于SQL用于解析多行数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！