php - MySQL/PHP - 需要能够生成具有比其他权重更大的某些列的查询结果

Question

我的任务是创建一个搜索功能，在搜索时，某些字段的权重会高于其他字段。

这是一个简化的例子。

汽车( table )
年份、品牌、型号、颜色、类型(列)

假设有人搜索以下内容:

年份:1968
品牌:福特
车型:野马
颜色:红色
类型:跑车

如果表中的汽车没有任何正确的字段，它们不应该显示，但如果记录有一些正确的字段但不是全部，它们仍然应该显示。但是某些字段的权重应该高于其他字段。

例如，也许它们的权重是这样的:
列 - 权重
年 - 30
制造 - 100
型号 - 85
颜色 - 10
类型 - 50

因此，如果一条记录与“品牌”字段和“型号”字段中的搜索相匹配，则该记录将位于“年份”、“颜色”和“类型”字段中匹配的记录之上，因为我们在每一列上设置了权重。

假设查询匹配数据库中两条记录的至少一个字段，它们应该根据权重按最相关的排序:

1971 年，Ford，Fairlane，蓝色，跑车(重量 = 185)
1968，道奇，Charger，红色，跑车(重量 = 90)

我一直在绞尽脑汁想弄清楚如何让它发挥作用。如果有人做过类似的事情，请告诉我如何让它发挥作用。

我想通过连接在 MySQL 中完成尽可能多的工作，我认为这会比在 PHP 中完成大部分工作更快地显示结果。但是这个问题的任何解决方案将不胜感激。

提前致谢

Answer 1

请耐心等待，这将是一个奇怪的查询，但它似乎对我有效。

SELECT SUM(
  IF(year = "1968", 30, 0) +
  IF(make = "Ford", 100, 0) +
  IF(model = "Mustang", 85, 0) +
  IF(color = "Red", 10, 0) +
  IF(type = "Sports Car", 50, 0)
) AS `weight`, cars.* FROM cars 
  WHERE year = "1968" 
  OR make = "Ford"
  OR model = "Mustang"
  OR color = "Red"
  OR type = "Sports Car"
GROUP BY cars.id
ORDER BY `weight` DESC;

基本上，这会将所有结果按其 id 分组(这对于 SUM() 函数是必需的，对不同的字段进行一些计算并将权重作为总值返回，然后对其进行排序highest-lowest。此外，这只会返回其中一列与提供的值匹配的结果。

由于我没有您的数据库的精确副本，请在您这边运行一些测试，如果有任何需要调整的地方，请告诉我。

预期结果:

+============================================================+
| weight | year | make      | model    | color  | type       |
|============================================================|
| 130    | 1968 | Ford      | Fairlane | Blue   | Roadster   |
| 100    | 2014 | Ford      | Taurus   | Silver | Sedan      |
| 60     | 2015 | Chevrolet | Corvette | Red    | Sports Car |
+============================================================+

因此，如您所见，结果将列出最接近的匹配项，在本例中是两辆福特 (+100) 汽车、一辆 1968 年的汽车 (+30) 和一辆红色跑车 (10 + 50)最接近的匹配项(使用您的标准)

还有一件事，如果您还想显示其余结果(即权重匹配分数为 0 的结果)，只需删除 WHERE ... OR ...，这样它将检查所有记录。干杯!

根据下面的评论，检查数据透视表上 LEFT JOIN 后的权重:

SELECT SUM(
  IF(cars.year = "1968", 30, 0) +
  IF(cars.make = "Ford", 100, 0) +
  IF(cars.model = "Mustang", 85, 0) +
  IF(cars.color = "Red", 10, 0) +
  IF(types.name = "Sports Car", 50, 0)
) AS `weight`, cars.*, types.* FROM cars 
LEFT JOIN cars_types ON cars_types.car_id = cars.id
LEFT JOIN types ON cars_types.type_id = types.id
  WHERE year = "1968" 
  OR cars.make = "Ford"
  OR cars.model = "Mustang"
  OR cars.color = "Red"
  OR types.name = "Sports Car"
GROUP BY cars.id
ORDER BY `weight` DESC;

这是 LEFT JOIN 的实践图片:

如您所见，Cobalt 匹配颜色(银色)和型号(Cobalt)(85 + 10)，而 Calibre 匹配类型(跑车)(50)。是的，我知道道奇口径不是跑车，这是为了举例。希望对您有所帮助!