SQL:如何以某种任意方式对查询结果进行排序/排序? [英] SQL: How to sort / order query result in somewhat arbitrary manner?

问题描述

我需要以非常规的顺序对查询结果进行排序.

I need to sort the results of a query in an UNCONVENTIONAL order.

说，我搜索主席".我希望将从椅子开始"的所有记录首先按字母顺序列出，然后将包含椅子"的其他记录随后按字母顺序列出.因此，蓝色椅子"将在蓝色椅子"之后列出.

Say, I do a search for "Chair". I want all the records STARTING with Chair to be listed first alphabetically, then other records that CONTAIN "chair" to be listed afterwards in alphabetical order. So "Blue Chair" would be listed after "Chair Blue" for example.

请注意开始"和包含"条件. 我应该如何实施这种排序?谢谢.

Please note the STARTING and CONTAINING conditions. How should I implement this sort? Thanks.

推荐答案

这看起来如何?

SELECT *, 1 as sort_pref FROM table WHERE column LIKE 'CHAIR%'
UNION
SELECT *, 2 as sort_pref FROM table WHERE column LIKE '%CHAIR%' AND column NOT LIKE 'CHAIR%'
ORDER BY sort_pref, column

这篇关于SQL:如何以某种任意方式对查询结果进行排序/排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！