根据基于图书元关键字的已购买图书查找相似的图书 [英] find similar books based on purchased books based on book meta keywords

问题描述

作为标题的建议，我想根据基于meta关键字的客户购买的图书来查找类似的图书.下面的查询有效，但是我被告知可以简化.

As the title suggest i want to find similar books based on customer purchased books based on the meta keywords. Below query works but Ive been told this can be simplified.

SELECT DISTINCT oth.book 
FROM book_meta_keywords oth
     INNER JOIN books b ON b.id = oth.book
     , (SELECT bmk.meta_keyword AS metaKeyword, bmk.book AS book 
        FROM books b
        INNER JOIN customers_books cvb ON cvb.book = b.id
        INNER JOIN book_meta_keywords bmk ON bmk.book = b.id
        WHERE cvb.customer = 1 ) AS allCustomerPurchasedBooksMeta 
WHERE oth.meta_keyword = allCustomerPurchasedBooksMeta.metaKeyword 
     AND oth.book != allCustomerPurchasedBooksMeta.book 
     AND b.status = 'GOOD'

表结构

book_meta_keywords
book       meta_keyword
1            history
1            culture
2            culture
3            facts

books
id    status
1      GOOD
1      GOOD 
2       GOOD
3       GOOD

customers_books
book       customer
1            90

我应该得到输出书-2.

I should get the output book - 2.

希望我的表数据对您有用，如果不让我知道很高兴对其进行纠正.

Hope my table data works for you if not let me know happy to correct it.

更新

我现在正在使用以下查询

I am using the below query now

SELECT bmk2.book
            FROM customers_books cb
            INNER JOIN book_meta_keywords bmk1 
                ON  bmk1.book = cb.book
            INNER JOIN book_meta_keywords bmk2 
                ON bmk2.meta_keyword = bmk1.meta_keyword
                AND bmk2.book <> bmk1.book
            INNER JOIN books b ON b.id = bmk2.book
            
            WHERE cb.customer = 1 AND b.status = 'PUBLISHED'

            GROUP BY bmk2.book
            ORDER BY MAX(b.modified_date) DESC      

此查询返回

13
3
11

但是我期望只有3个，因为客户已经购买了13和11本书. 要查看客户购买的图书，您可以运行以下查询，返回13、11

But I am expecting only 3 because customer has already purchased books 13 and 11. To see customer purchased books you can run the below below query which returns 13, 11

SELECT c.book FROM customers_books c WHERE c.customer = 1

完整的表结构和SQL在下面的数据库小提琴中提供. https://www.db-fiddle.com/f/tovUePp2WVffXLcuaxmJ8K/5

Full table structure and SQL is provided in the DB fiddle below. https://www.db-fiddle.com/f/tovUePp2WVffXLcuaxmJ8K/5

您认为这是什么问题.我认为 这行AND bmk2.book <> bmk1.book不起作用.

what do you think is the issue. I think this line AND bmk2.book <> bmk1.book isnt working.

推荐答案

您可以通过联接来做到这一点:

You can do this with joins:

select bmk2.book
from customer_books cb
inner join book_meta_keyword bmk1 
    on  bmk1.book = cb.book
inner join book_meta_keyword bmk2 
    on  bmk2.meta_keyword = bmk1.meta_keyword
    and bmk2.book <> bmk1.book
where cb.customer = 1

查询从客户1购买的书籍开始，然后带入相应的关键字，最后得到所有具有相同关键字的所有其他书籍.

The query starts from books that customer 1 purchased, then bring the corresponding keywords, and finally get all other books that have any keyword in common.

注意:

• 如果各本书中有多个匹配的关键字，则结果集中将出现重复的关键字.在这种情况下，请使用select distinct

您不需要表book即可获得所需的结果-如果出于某些原因需要，可以再添加一个联接

You don't need table book to get the result you want - if needed for some reason, you can bring it with one more join

这篇关于根据基于图书元关键字的已购买图书查找相似的图书的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！