排序依据而不是筛选依据 - To sort by rather than filter by

To sort by rather than filter by

本文关键字：筛选排序 | 更新日期: 2023-09-27

我在下面的现有查询工作完美。但是，我有一个新字段来确定显示这些结果的区域顺序。

例如，如果用户正在浏览网站一（ID 123），则所有记录对于区域 123、234 和 543，必须显示区域 ID 123 的记录，然后根据我现有的顺序记录 234 和 543。这是什么区域的变量将始终$AreaID 。

请记住，我不是在按该区域过滤记录，我只是想按照 ASC 顺序将该区域的记录放在其他记录之上cont_id

我在任何地方都找不到一个例子，甚至给我一个可以实验的方向。我想我必须以某种方式单独选择它们吗？

$sql = "select * 
    from 202_articles 
    where cont_date_published <= CURDATE() 
    and cont_time_published < CURTIME() 
    and cont_category_id = $TypeID 
    ORDER by cont_id ASC 
    LIMIT 25";

任何帮助表示赞赏。

假设您有一个名为 AreaID 的列，则首先使用大小写按该条件进行排序：

$sql = "select * 
from 202_articles 
where cont_date_published <= CURDATE() 
and cont_time_published < CURTIME() 
and cont_category_id = $TypeID 
ORDER by
CASE WHEN AreaID = $AreaID THEN 0 ELSE 1 END,
AreaID,
cont_id ASC 
LIMIT 25";

您可以使用

一个简单的UNION JOIN来选择具有特定cont_id的所有内容，然后选择其他所有内容，然后按cont_id ASC

SELECT
    *
FROM
    202_articles
WHERE
    cont_date_published <= CURDATE()
AND cont_time_published < CURTIME()
AND cont_category_id = $TypeID
AND cont_id = $AreaID
UNION
    SELECT
        *
    FROM
        202_articles
    WHERE
        cont_date_published <= CURDATE()
    AND cont_time_published < CURTIME()
    AND cont_category_id = $TypeID
    ORDER BY
        cont_id ASC
    LIMIT 25

POC： http://sqlfiddle.com/#!9/3ae07/1

$sql = "select * 
from 202_articles 
where cont_date_published <= CURDATE() 
and cont_time_published < CURTIME() 
and cont_category_id = $TypeID 
ORDER by cont_id ASC 
LIMIT 25";

如果您知道该区域，则可以使用以下查询而不是上述查询。

$sql = "select * 
from 202_articles 
where cont_date_published <= CURDATE() 
and cont_time_published < CURTIME() 
and cont_category_id = $TypeID 
ORDER by cont_id='$userarea' ASC, cont_id ASC 
LIMIT 25";