从带有关键字字段的主表和带有各个关键字的标签表上的选择匹配更新标签关系表

发布于 2024-11-18 15:46:45 字数 1032 浏览 3 评论 0原文

我将软件 PAD 文件数据库从 http://paddatabase.net/download.html 导入到 Microsoft Access 中一个名为 main 的表：

MAIN
-----
ID
ProgramName
Keywords
.
.

我创建了两个新表：Tags 和 TagSoftwareRel。

Tags
--------
ID
Tag


TagSoftwareRel
--------------
ID
SoftwareID <- (MainTable)
TagID <- tags table

我从“关键字”字段中提取了所有关键字，作为“标签”表中的单个单词。 Main 中的 keywords 字段如下所示：

Keywords
Paul, animated, moving monk, paulaner
Image,Imaging,Graphics,Rotate,Resize,Effects,
Sharpen,Blur,JPEG,TIFF,BMP,WBMP,PSD,GIF,PDF,Format,ICM,ICC,CMYK,
thumbnail,Convert,Display,AJAX,AVI,red-eye removal,lossless JPEG  transform     
correction, rich,internet,applications,ebooks,webmaster,authoring,

我想要做的是创建一个 SQL 查询，通过使用 where Tags.tag 将 tagID 从标签表插入 tagsoftwarerel.tagid 并将主表中的相关 softwareID 插入 tagsoftwarerel.softwareid就像 main.keywords

我有点不知道从哪里开始。

由于它是一个公共数据库，我可以向任何感兴趣的人提供数据库的副本。

任何帮助将不胜感激。谢谢。

原文

I imported the Software PAD File Database from http://paddatabase.net/download.html into Microsoft Access in a table called main:

MAIN
-----
ID
ProgramName
Keywords
.
.

I created two new tables: Tags and TagSoftwareRel.

Tags
--------
ID
Tag


TagSoftwareRel
--------------
ID
SoftwareID <- (MainTable)
TagID <- tags table

I extracted all the keywords from the field Keywords as individual words in the Tags table. They Keywords field from Main looks like this:

Keywords
Paul, animated, moving monk, paulaner
Image,Imaging,Graphics,Rotate,Resize,Effects,
Sharpen,Blur,JPEG,TIFF,BMP,WBMP,PSD,GIF,PDF,Format,ICM,ICC,CMYK,
thumbnail,Convert,Display,AJAX,AVI,red-eye removal,lossless JPEG  transform     
correction, rich,internet,applications,ebooks,webmaster,authoring,

What I want to do is create a SQL Query which Inserts the tagID from the tags table into tagsoftwarerel.tagid and the related softwareID from the main table into tagsoftwarerel.softwareid by using a where tags.tag like main.keywords

I'm sort of at loss of where to start with this.

As it is a public DB I can provide a copy of the database to anyone interested.

Any assistance would be most appreciated. Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

司马昭之心 2024-11-25 15:46:45

我假设 TagSoftwareRel 中的字段 ID 是自动值或标识。这意味着该值是通过插入新行自动创建的：
据我所知，您已经填写了标签表。

下面是一个将填充 TagSoftarerel-Table 的查询：

Insert into TagSoftarerel (SoftwareID, TagID)
Select m.Id, 
       (Select T.TagId from Tag T where T.Tag = m.Keyword) as TagId 
from MAIN m

建议：
我认为你可以找到更好的解决方案。标签信息在您的解决方案中是多余的。
如果您只是将 Tag.Id 添加到主表中，则可以删除关键字列并将标签信息仅存储在它所属的标签表中。

I assume that the field ID from the TagSoftwareRel is an autovalue or identity. That means the value is created automaticly by inserting a new row:
I understand that you already filled the Tags-Table.

Here is a query which would fill the TagSoftarerel-Table:

Insert into TagSoftarerel (SoftwareID, TagID)
Select m.Id, 
       (Select T.TagId from Tag T where T.Tag = m.Keyword) as TagId 
from MAIN m

Advice:
I think you could find a better solution. The Tag-information is redundant in your solution.
If you would just add the Tag.Id to the main-table you could get rid of the Keyword column and store the tag-information solely in the tag table, where it belongs to.

回复收藏 0 原文

~没有更多了~