如何确保一条记录始终在mysql中给定的结果集中位于顶部? [英] how to make sure a record is always at the top in a given resultset in mysql?

问题描述

我有一个搜索查询，该查询根据输入的参数生成结果集，并且可以根据不同的参数对结果进行排序(ASC/DESC):价格，持续时间等(具有分页功能，最多10条记录)

I have a search query which yields a resultset based on input parameters and the result can be sorted (ASC/DESC) based on different parameters: price, duration etc (with pagination in place and limit of 10 records)

我现在有一个要求，如果我传入了id，我希望在给定结果集中将相应的记录粘贴在顶部.

I now have a requirement wherein, if I have an id passed in, I'd like the corresponding record to be made to sticky at the top in the given resultset.

让我们说我们有一个这样的包装表:

Lets say we have a package table like this:

Package
 - id
 - name
 - mPrice
 - vPrice
 - duration

// Searching pkg based on Price (in DESC order) where name = Thailand
sqlQuery = 
"SELECT p.id, p.name, p.mPrice, p.vPrice FROM package p 
WHERE p.name = LOWER('Thailand') 
ORDER BY (p.mPrice + p.vPrice) DESC 
LIMIT 10"

让我们假设完整的结果集是20条ID为1到20的记录.我现在需要返回ID 14的记录总是在最上面.我提出了以下查询，但这不起作用:

Let's assume the complete resultset is 20 records with ids 1 to 20. I'm now required to return record with id 14 to always be at the top. I came up with the following query, but this isn't working:

sqlQuery = 
"SELECT p.id, p.name, p.mPrice, p.vPrice FROM package p 
WHERE p.name = LOWER('Thailand') or p.id = 14
ORDER BY CASE 
WHEN p.id=14 then 0 
else (p.mPrice + p.vPrice) 
end DESC 
LIMIT 10"

我对这为什么不起作用的猜测:在order by子句之后，结果集按降序排序，然后在10条记录处被截断. id = 14的记录可能不属于该截断集.这是正确的吗?

My guess as to why this is not working: After order by clause, the resultset is sorted in descending order, which is then truncated at 10 records. The record with id=14 may not be a part of this truncated set. Is this correct ?

如何获取ID = 14的记录粘贴在顶部?

How do I get record with id=14 to stick at the top ?

推荐答案

ORDER BY (p.id=14) DESC, (p.mPrice=p.vPrice) DESC

p.id=14如果条件为true，则返回1，否则返回0，因此降序排序会将所需的行排到顶部.

p.id=14 returns 1 if the condition is true, 0 otherwise, so sorting descending brings the desired row to the top.

从比较中返回数字是MySQL的一项功能，您可以使用标准SQL编写:

Returning a number from a comparison is a MySQL feature, with standard SQL you would write:

ORDER BY CASE WHEN (p.id=14) THEN 0 ELSE 1 END,
         CASE WHEN (p.mPrice=p.vPrice) THEN 0 ELSE 1 END

---

我发现它比UNION更易于阅读，并且效果可能会更好.

---

I find this easier to read than the UNION, and it might perform better.

这篇关于如何确保一条记录始终在mysql中给定的结果集中位于顶部?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！