python - 将项目推到堆栈的末尾(第 2 部分)

Question

我有以下代码获取要呈现给用户的项目:

SELECT * FROM main_cue WHERE group_id IN (1,2,3,4) GROUP BY group_id;
>>> 1,2,3,4

但是，我想为此提供一个特定的顺序，这样 (1) 我有一个要推送到堆栈末尾的项目列表 按照我指定的确切顺序 和 (2 ) 不在列表中的项目将按 id ASC 排序。例如:

items_to_push_to_end_of_stack_in_this_exact_order = [3,2]

新的顺序将是:

>>> [1,4,3,2]
1) 1 # first item by ID asc, not in `items_to_push_to_end_of_stack_in_this_exact_order`
2) 4 # second item by ID asc, not in `items_to_push_to_end_of_stack_in_this_exact_order`
3) 3 # items_to_push_to_end_of_stack_in_this_exact_order
4) 2 # items_to_push_to_end_of_stack_in_this_exact_order

我如何在 mysql 中执行此操作？

Answer 1

如果您尝试指定顺序，请使用order by:

ORDER BY

    // First, Put "2" and "3" at the end of the stack
   (group_id IN (2, 3)) ASC,

   // Second, order the items not in the list by the `group_id`
   (CASE WHEN group_id NOT IN (2, 3) THEN group_id END) ASC,

   // Third, order the items in the list by the ordering you specify
   FIELD(group_id, 3, 2)

这有点复杂。第一个子句将“2”和“3”放在末尾。第二个按 group_id 对其余的进行排序。第三个按照您指定的顺序对最后一组进行排序。