SQL Order按Match Against Correlation结果，并根据卖家排名显示价格

Question

希望根据用户搜索的"相关性"以及排名最高的卖家的价格来显示结果。我追求的一个活生生的例子是亚马逊的搜索结果，现在我知道他们的算法非常复杂，但我追求的是简化版本。

假设我们搜索"跳线"，返回的结果是与"跳线"相关的产品，但价格并不总是最便宜的。这是基于卖家的排名。排名最高的卖家会显示他/她的价格。

这是我一直在做的事情，但没有给我上面提到的预期结果，老实说，我认为这不是很有效。

SELECT a.catalogue_id, a.productTitle, a.prod_rank, b.catalogue_id, b.display_price, b.sellers_rank
FROM 
(
SELECT c.catalogue_id,
c.productTitle,
MATCH(c.productTitle) AGAINST ('+jumper*' IN BOOLEAN MODE) AS prod_rank 
FROM catalogue AS c
WHERE c.catalogue_id IN (1, 2, 3)
) a
JOIN 
(
SELECT inventory.catalogue_id, 
inventory.amount AS display_price,
(accounts.comsn + inventory.quantity - inventory.amount) AS sellers_rank
FROM inventory 
JOIN accounts ON inventory.account_id = accounts.account_id 
WHERE inventory.catalogue_id IN (1, 2, 3)
) AS b
ON a.catalogue_id = b.catalogue_id 
ORDER BY a.prod_rank DESC
LIMIT 100;

示例表：

Accounts:
----------------------------
account_id | comsn
----------------------------
1          | 100
2          | 9999


Catalogue:
----------------------------
catalogue_id | productTitle
----------------------------
1            | blue jumper
2            | red jumper
3            | green jumper


Inventory:
-----------------------------------------------
product_id | catalogue_id | account_id | quantity | amount |
-----------------------------------------------
1           | 2           | 1          | 6        | 699
2           | 2           | 2          | 2        | 2999

预期结果：

Product Title:
red jumper
Amount:
29.99 (because he/she has sellers rank of: 7002)

Answer 1

首先，您应该将结果仅限于第一个子查询的匹配项：

其次，您应该消除第二个子查询：

SELECT p.catalogue_id, p.productTitle, p.prod_rank,
i.amount as display_price,
(a.comsn + i.quantity - i.amount) 
FROM (SELECT c.catalogue_id, c.productTitle,
MATCH(c.productTitle) AGAINST ('+jumper*' IN BOOLEAN MODE) AS prod_rank 
FROM catalogue AS c
WHERE c.catalogue_id IN (1, 2, 3)
HAVING prod_rank > 0
) p JOIN 
inventory i
ON i.catalogue_id = c.catalogue_id join
accounts a
ON i.account_id = a.account_id 
ORDER BY c.prod_rank DESC
LIMIT 100;

我不确定你是否能去掉最后的ORDER BY。CCD_ 2与CCD_ 3在这方面可能有点棘手。但只有按火柴排序才有帮助。