MySQL 使用 FIND_IN_SET 对多个方向进行排序 [英] MySQL order multiple directions using FIND_IN_SET

问题描述

我有一个查询，它按 FIND_IN_SET 排序，然后是辅助 ORDER 字段...date_ added.

I have a query that orders by FIND_IN_SET and then a secondary ORDER field...date_added.

我现在想做的是:

ORDER BY FIND_IN_SET(status, '1,2,3'), date_added ASC
AND
ORDER BY FIND_IN_SET(status, '4,5'), end_date DESC

所以我想按 date_ added ASC 订购状态 1,2 和 3，并按 end_date DESC 订购状态 4 和 5，但我想要状态 1 的所有结果，2 和 3 在状态 4 和 5 之前输出.

so I want to order status 1,2 and 3 by date_added ASC and the status 4 and 5 by end_date DESC but I would like all results for status 1,2 and 3 to be outputted before status 4 and 5.

这可以使用单个查询来完成吗?

Can this be done using a single query?

推荐答案

ORDER BY 不是这样工作的.您在同一个子句中提供所有条件，并用逗号分隔.

That's not how ORDER BY works. You provide all the criteria in the same clause, separating them with commas.

快速阅读排序行一章监督.

更新:

您既没有提供表定义也没有提供数据样本，因此很难弄清楚您的想法，但我会尝试猜测.FIND_IN_SET() 函数返回第一个参数在第二个参数中的索引位置:

You provide neither table definitions nor data samples so it's pretty hard to figure out what you have in mind but I'll try to guess. The FIND_IN_SET() function returns the index position of the first argument within the second argument:

SELECT
    FIND_IN_SET(2, '1,2,3') AS found,
    FIND_IN_SET(4, '1,2,3') AS not_found,
    FIND_IN_SET(NULL, '1,2,3') AS null_needle

...返回:

found | not_found | null_needle
    2 |         0 |        NULL

我知道您想使用此信息的一部分来组成 ORDER BY 组件(是否在大海捞针中找到了针，丢弃其位置).然后，检查返回值是否大于零应该就足够了:

I understand you want to use part of this information to compose an ORDER BY component (whether the needle is found in the haystack, discarding its position). Then, checking that the returned value is greater than zero should be enough:

ORDER BY
    CASE
        WHEN FIND_IN_SET(status, '1,2,3')>0 THEN date_added
        WHEN FIND_IN_SET(status, '4,5')>0 THEN end_date DESC
    END

这篇关于MySQL 使用 FIND_IN_SET 对多个方向进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！