多对多数据关系的文本搜索

发布于 2025-01-03 05:48:54 字数 901 浏览 1 评论 0原文

我知道这个问题之前肯定已经回答过，但我就是找不到匹配的问题。

我想使用 LIKE '%keyword%' 在 MSSQL 数据库中搜索多对多数据关系并将其简化为一对一结果集。这两个表通过链接表连接起来。这是我所讨论内容的一个非常简化的版本：

Books:
book_ id  title
1         Treasure Island
2         Poe Collected Stories
3         Invest in Treasure Islands

Categories:
category_id  name
1            Children
2            Adventure
3            Horror
4            Classic
5            Money

BookCategory:
book_id   category_id
1         1
1         2
1         4
2         3
2         4
3         5

我想要做的是搜索标题中的短语（例如 '%treasure island%'）并获取包含以下内容的匹配图书记录搜索字符串和每本书附带的单个最高匹配的 Categories 记录 - 我想丢弃较小的类别记录。换句话说，我正在寻找这个：

book_id  title                       category_id  name
1        Treasure Island             4            Classic
3        Invest in Treasure Islands  5            Money

有什么建议吗？

原文

I know this must have been answered before here, but I simply can't find a matching question.

Using a LIKE '%keyword%', I want to search a many-to-many data relationship in a MSSQL database and reduce it to a one-to-one result set. The two tables are joined through a linking table. Here's a very simplified version of what I'm talking about:

Books:
book_ id  title
1         Treasure Island
2         Poe Collected Stories
3         Invest in Treasure Islands

Categories:
category_id  name
1            Children
2            Adventure
3            Horror
4            Classic
5            Money

BookCategory:
book_id   category_id
1         1
1         2
1         4
2         3
2         4
3         5

What I want to do is search for a phrase in the title (e.g. '%treasure island%') and get matching Books records that contain the search string and the single highest matching Categories record that goes with each book -- I want to discard the lesser category records. In other words, I'm looking for this:

book_id  title                       category_id  name
1        Treasure Island             4            Classic
3        Invest in Treasure Islands  5            Money

Any suggestions?

分享到QQ

分享到微博

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

发布评论

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

绅士风度i 2025-01-10 05:48:54

试试这个。过滤您的查找表，然后加入：

With maxCategories AS
 (select book_id, max(category_id) as category_id from BookCategory group by book_id)
select Books.book_id, Books.Title, Categories.category_id, Categories.name
from Books 
inner join maxCategories on (Books.book_id = maxCategories.book_id)
inner join Categories on (Categories.category_id = maxCategories.category_id)
where Books.title like '%treasure island%'

Try this. Filter your lookup table, then join:

With maxCategories AS
 (select book_id, max(category_id) as category_id from BookCategory group by book_id)
select Books.book_id, Books.Title, Categories.category_id, Categories.name
from Books 
inner join maxCategories on (Books.book_id = maxCategories.book_id)
inner join Categories on (Categories.category_id = maxCategories.category_id)
where Books.title like '%treasure island%'

回复收藏 0 原文

对你再特殊 2025-01-10 05:48:54

尝试：

select * from
(select b.*, 
        c.*, 
        row_number() over (partition by bc.book_id 
                           order by bc.category_id desc) rn
 from Books b
 join BookCategory bc on b.book_id = bc.book_id
 join Categories c on bc.category_id = c.category_id
 where b.name like '%treasure island%') sq
where rn=1

Try:

select * from
(select b.*, 
        c.*, 
        row_number() over (partition by bc.book_id 
                           order by bc.category_id desc) rn
 from Books b
 join BookCategory bc on b.book_id = bc.book_id
 join Categories c on bc.category_id = c.category_id
 where b.name like '%treasure island%') sq
where rn=1

回复收藏 0 原文

~没有更多了~