按特定字段值排序 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/398468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Ordering SQL query by specific field values
提问by Cyprus106
I've got a sql query (using Firebird as the RDBMS) in which I need to order the results by a field, EDITION. I need to order by the contents of the field, however. i.e. "NE" goes first, "OE" goes second, "OP" goes third, and blanks go last. Unfortunately, I don't have a clue how this could be accomplished. All I've ever done is ORDER BY [FIELD] ASC/DESC and nothing else.
我有一个 sql 查询（使用 Firebird 作为 RDBMS），我需要在其中按字段 EDITION 对结果进行排序。但是，我需要按字段的内容进行排序。即“NE”排在第一位，“OE”排在第二位，“OP”排在第三位，空白排在最后。不幸的是，我不知道如何实现这一点。我所做的只是按 [FIELD] ASC/DESC 排序，没有别的。
Any suggestions?
有什么建议？
Edit: I really should clarify: I was just hoping to learn more here. I have it now that I just have multiple select statements defining which to show first. The query is rather large and I was really hoping to learn possibly a more effecient way of doing this: example:
编辑：我真的应该澄清一下：我只是希望在这里了解更多。我现在有了，我只有多个选择语句来定义首先显示哪个。查询相当大，我真的希望学习可能更有效的方法：例如：
SELECT * FROM RETAIL WHERE MTITLE LIKE 'somethi%' AND EDITION='NE'
UNION
SELECT * FROM RETAIL WHERE MTITLE LIKE 'somethi%' AND EDITION='OE'
UNION
SELECT * FROM RETAIL WHERE MTITLE LIKE 'somethi%' AND EDITION='OP'
UNION (etc...)
回答by Charles Bretana
Order By Case Edition
When 'NE' Then 1
When 'OE' Then 2
When 'OP' Then 3
Else 4 End
回答by Pulsehead
SELECT
/*other fields*/
CASE WHEN 'NE' THEN 1
WHEN "OE" THEN 2
WHEN "OP" THEN 3
ELSE 4
END AS OrderBy
FROM
/*Tables*/
WHERE
/*conditions*/
ORDER BY
OrderBy,
/*other fields*/
回答by Peter T. LaComb Jr.
Add those values to another table with a numeric column for their rank:
将这些值添加到另一个带有数字列的表中：
Edition Rank
NE 1
OE 2
OP 3
Join the tables, and sort on the RANK field.
连接表，并在 RANK 字段上排序。
回答by RedFilter
Try:
尝试：
select *
from MyTable
order by
case [FIELD]
when 'NE' then 1
when 'OE' then 2
when 'OP' then 3
when '' then 4
else 5
end
回答by user49953
Try this:
尝试这个：
ORDER BY FIND_IN_SET(EDITION, 'NE,OE,OP,')
回答by Dilip Kr Singh
CREATE TABLE #TMP
(
ID INT IDENTITY(1,1),
NAME VARCHAR(100),
)
INSERT INTO #TMP
SELECT 'ASHISH'
UNION ALL
SELECT 'CHANDAN'
UNION ALL
SELECT 'DILIP'
UNION ALL
SELECT 'ESHA'
UNION ALL
SELECT 'FIZA'
UNION ALL
SELECT 'MAHESH'
UNION ALL
SELECT 'VIPUL'
UNION ALL
SELECT 'ANIL'
-- I want to sort NAME column from value 'DILIP' then query will be as bellow
SELECT * FROM #TMP ORDER BY CASE WHEN NAME='DILIP' THEN '1' ELSE NAME END ASC
DROP TABLE #TMP
回答by H.He
How about this?
这个怎么样？
SELECT *
FROM RETAIL
WHERE MTITLE LIKE 'somethi%'
ORDER BY POSITION(EDITION, ' OP OE NE') DESC
If substr is empty string, the result is 1. If no match is found, the result is 0.
如果 substr 为空字符串，则结果为 1。如果未找到匹配项，则结果为 0。
Position()
位置（）
Added in Firebird 2.1
在 Firebird 2.1 中添加
reference: https://firebirdsql.org/refdocs/langrefupd21-intfunc-position.html
参考：https: //firebirdsql.org/refdocs/langrefupd21-intfunc-position.html
回答by Arioch 'The
SELECT * FROM (
SELECT 1 as Rank, R.* FROM RETAIL R WHERE MTITLE LIKE 'somethi%' AND EDITION='NE'
UNION ALL
SELECT 2 as Rank, R.* FROM RETAIL R WHERE MTITLE LIKE 'somethi%' AND EDITION='OE'
UNION ALL
SELECT 3 as Rank, R.* FROM RETAIL R WHERE MTITLE LIKE 'somethi%' AND EDITION='OP'
UNION ALL (etc...)
) ORDER BY 1
回答by Andreu Serra Candela
SELECT (CASE WHEN 'NE' THEN 1
WHEN "OE" THEN 2
WHEN "OP" THEN 3
ELSE 4) as orden,* FROM Retail WHERE MTITLE LIKE 'somethi%'
AND EDITION IN ('NE', 'OE', 'OP', '') ORDER BY Orden
