MySQL MATCH 多个关键字

发布于 2024-11-17 13:32:29 字数 633 浏览 0 评论 0原文

我正在构建一个功能，可以查找与用户在表单中输入的内容类似的项目。用户填写两个字段：类型和制造商

我想在数据库中搜索与其相应值匹配的记录，并首先返回最佳匹配项。

我有一个为每个字段分配分数的查询：

SELECT id, manuf, model, type, MATCH(manuf) AGAINST('BMW') AS `score_manuf`, MATCH(type) AGAINST('Car') AS `score_type` FROM `items`

这实际上有效并产生分数。

现在我想计算总分，即两个分数的总和，以便稍后用于对结果进行排序：

SELECT id, manuf, model, type, MATCH(manuf) AGAINST('BMW') AS `score_manuf`, MATCH(type) AGAINST('Car') AS `score_type`, (score_manuf + score_type) as 'score' FROM `items`

当我运行此查询时，我收到一条错误消息，指出“score_manuf”是未知列。

我不能对别名字段运行算术吗？或者也许还有另一种方法来完成我想做的事情？

原文

I'm building a feature that looks-up similar items to what user is inputting in the form.
The user fills in two fields: type and manufacturer

I want to search for records in my database that match their corresponding values and return the best matches first.

I have a query that assigns a score to each field:

SELECT id, manuf, model, type, MATCH(manuf) AGAINST('BMW') AS `score_manuf`, MATCH(type) AGAINST('Car') AS `score_type` FROM `items`

This actually works and produces the scores.

Now I want to calculate the total score, that is the sum of two scores, to be later used to sort the results:

SELECT id, manuf, model, type, MATCH(manuf) AGAINST('BMW') AS `score_manuf`, MATCH(type) AGAINST('Car') AS `score_type`, (score_manuf + score_type) as 'score' FROM `items`

When I run this query, I get an error saying that "score_manuf" is an unknown column.

Can I not run arithmetic on alias fields? Or perhaps there is another way to accomplish what I'm trying to do?

分享到QQ

分享到微博

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

发布评论

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

岛歌少女 2024-11-24 13:32:29

SELECT id, manuf, model, type,
       @m := MATCH(manuf) AGAINST('BMW') AS score_manuf,
       @t := MATCH(type) AGAINST('Car') AS score_type,
       @m + @t as score
  FROM items;

这是定义用户定义变量的方式

SELECT id, manuf, model, type,
       @m := MATCH(manuf) AGAINST('BMW') AS score_manuf,
       @t := MATCH(type) AGAINST('Car') AS score_type,
       @m + @t as score
  FROM items;

This is the way you define user-defined variables

回复收藏 0 原文

~没有更多了~