SQL-按最接近的值对记录进行排序 [英] SQL - Order records by the closest value

问题描述

我为产品详细信息创建了一个API
在此API中，我还需要附加类似的产品作为响应。

I have created one API for product details In this API I also need to attach similar products in response.

对于类似产品，情况类似于以下
1）价格明智（+10和-10）
2）然后在类别明智之后（来自同一类别）

So for similar products scenario are like following 1) Price wise (+10 and -10) 2) Then after category wise (From same category)

例如
我有ID为＃30的产品，价格为$ 30，类别为啤酒
因此，类似的产品列表将像以下
一样首先显示所有产品范围之间的所有啤酒类别产品+30的+10和-10（我的意思是黑白范围为20到40）
然后贴上属于同一类别啤酒的其他产品，其最近价格为$ 30
假设产品价格相同类别跟随
$ 10，$ 17，$ 45，$ 42，$ 50

e.g. I have product with id #30 and price with $30 and category with "beer" SO the similar products listing will be like following First show all products beer category products which are come between range +10 and -10 of $30 (I mean b/w range 20 to 40) Then after attach other products which are belongs from same category "beer" with closest price of $30 Suppose products price with same category are following $10, $17, $45, $42, $50

，因此产品分类如下：壁橱为$ 30
$ 42（$ 42- $ 30 = 12），$ 17（$ 30-$ 17 = 13），$ 45（$ 45-$ 30 = $ 15），$ 10（$ 30-$ 10 = $ 20），$ 50（$ 50-$ 30 = $ 20）

so the products will sort as following as closet to $30 $42 ($42 - $30 = 12), $17 ($30 - $17 = 13), $45 ($45 - $30 = $15), $10 ($30 - $10 = $20), $50 ($50 - $30 = $20)

我在查询下面创建的+10 -10范围内的类似产品

for similar products in +10 -10 range i created below query

similar_price = Product.includes(:sub_category, :brand, :product_type, :category).in_stock.where("(actual_price BETWEEN ? AND ? AND category_id = ? ) ", min_price, max_price, self.category_id)

现在我需要价格最优惠的rder产品。
如何用Rails中的postgres查询解决此问题？

now i need to order products with closest price. How can fix this issue with postgres query in rails ?

推荐答案

是的，您可以通过简单地执行此操作像这样的订单查询

Yes you can perform this operation by using just simple order query as like following

`Product.where("product.category_id = ?", "category_id").order("abs(products.price - #{your_selected_product_price})")`

这篇关于SQL-按最接近的值对记录进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！