SQL：ORDER BY 使用特定列中的子字符串...可能吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8187828/
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
SQL: ORDER BY using a substring within a specific column... possible?
提问by Brds
I have a database whose columns are npID, title, URL, and issue.
我有一个数据库，其列是 npID、标题、URL 和问题。
Here is an example of two years' entries:
以下是两年条目的示例：
npID title URL issue
88 EMR Newsletter a.com 2010 Third_Quarter
89 EMR Newsletter b.com 2010 Second_Quarter
43 EMR Newsletter c.com 2010 First_Quarter
47 EMR Newsletter d.com 2009 Winter
45 EMR Newsletter e.com 2009 Summer
46 EMR Newsletter f.com 2009 Spring
44 EMR Newsletter g.com 2009 Fall
What I would like to do is be able to sort the results based on substrings within the "issue" column. However, until 2010, the client used seasons as the header and in 2010, they started using quarters. Is there a way in the "ORDER BY" I can provide a list of words to sort by if/when they're found anywhere in the "issue" value?
我想要做的是能够根据“问题”列中的子字符串对结果进行排序。然而，直到 2010 年，客户才使用季节作为标题，并在 2010 年开始使用季度。在“ORDER BY”中有没有办法我可以提供一个单词列表来排序是否/何时在“问题”值中的任何位置找到它们？
I would like the end result to be something like this:
我希望最终结果是这样的：
npID title URL issue
43 EMR Newsletter c.com 2010 First_Quarter
89 EMR Newsletter b.com 2010 Second_Quarter
88 EMR Newsletter a.com 2010 Third_Quarter
47 EMR Newsletter d.com 2009 Winter
45 EMR Newsletter e.com 2009 Summer
46 EMR Newsletter f.com 2009 Spring
44 EMR Newsletter g.com 2009 Fall
回答by Tom H
You can put a CASEstatement in the ORDER BYto accomplish this. A better route would be to change the application and table to actually store this relevant data in columns where it belongs when you have the development time to do that.
您可以在 中放置一个CASE语句ORDER BY来完成此操作。更好的方法是更改应用程序和表，以便在您有开发时间时将这些相关数据实际存储在它所属的列中。
ORDER BY
CAST(SUBSTRING(issue, 1, 4) AS INT) DESC, -- Year
CASE
WHEN issue LIKE '%First_Quarter' OR issue LIKE '%Winter' THEN 1
WHEN issue LIKE '%Second_Quarter' OR issue LIKE '%Spring' THEN 2
WHEN issue LIKE '%Third_Quarter' OR issue LIKE '%Summer' THEN 3
WHEN issue LIKE '%Fourth_Quarter' OR issue LIKE '%Fall' THEN 4
END
Order the seasons however you want. You could also order them in a specific way (Q1 followed by Spring, followed by Q2, etc.) by adjusting the CASEstatement.
随心所欲地订购季节。您还可以通过调整CASE语句以特定方式（Q1 后跟 Spring，后跟 Q2 等）对它们进行排序。
回答by Erwin Brandstetter
Standard SQL
标准 SQL
Try a CASEstatement in the ORDER BY:
尝试在 中CASE声明ORDER BY：
SELECT npID, title, URL, issue
FROM tbl
ORDER BY substring(issue, 1, 4) DESC
,CASE
WHEN substring(issue, 6, 100) IN ('Winter','First_Quarter') THEN 1
WHEN substring(issue, 6, 100) IN ('Summer','Second_Quarter') THEN 2
WHEN substring(issue, 6, 100) IN ('Spring','Third_Quarter') THEN 3
WHEN substring(issue, 6, 100) IN ('Fall', 'Fourth Quarter') THEN 4
ELSE 5
END;
Don't ask, why Winter -> Summer -> Spring- it's what the client wants! :)
不要问，为什么Winter -> Summer -> Spring- 这是客户想要的！:)
Optimize performance
优化性能
A "simple" CASE should perform better since the expression is only evaluated once.
And right(issue, -5)is equivalent to substring(issue, 6, 100), but a bit faster:
“简单” CASE 应该表现得更好，因为表达式只计算一次。
和right(issue, -5)等价于substring(issue, 6, 100)，但要快一点：
SELECT npid, title, url, issue
FROM tbl
ORDER BY left(issue, 4) DESC
,CASE right(issue, -5)
WHEN 'Winter' THEN 1
WHEN 'First_Quarter' THEN 1
WHEN 'Summer' THEN 2
WHEN 'Second_Quarter' THEN 2
WHEN 'Spring' THEN 3
WHEN 'Third_Quarter' THEN 3
WHEN 'Fall' THEN 4
WHEN 'Fourth Quarter' THEN 4
ELSE 5
END;
left()and right()have been added with PostgreSQL 9.1. The trick with right()is to use a negative numberto trim a constant number of characters from the left.
left()并right()已随 PostgreSQL 9.1 添加。诀窍right()是使用负数从左侧修剪恒定数量的字符。
Syntax variants
语法变体
These are equivalent (for strings of <= 100 characters):
这些是等效的（对于 <= 100 个字符的字符串）：
SELECT substring(issue from 6 for 100) AS substring1
,substring(issue, 6, 100) AS substring2
,substring(issue, 6) AS substring3
,substr(issue, 6, 100) AS substr1
,substr(issue, 6) AS substr2
,right(issue, -5) AS right0
FROM tbl
回答by Conrad Frix
Here's a variation of the same theme
这是同一主题的变体
ORDER BY
SUBSTRING(issue,1,4) Desc,
CASE SUBSTRING(issue,6, LEN(issue) - 5)
WHEN 'First_Quarter' THEN 1
WHEN 'Second_Quarter' THEN 2
WHEN 'Second_Quarter' THEN 3
WHEN 'Winter' then 1
WHEN 'Spring' then 2
WHEN 'Summer' then 3
WHEN 'Fall' then 4
END
回答by sorpigal
Try this approach (this is in T-SQL):
试试这个方法（这是在 T-SQL 中）：
select
*
from
your_table as t
order by
substring([issue], 1, 4) desc,
case substring([issue], 6, len([issue]) - 5)
when 'First_Quarter' then 1
when 'Second_Quarter' then 2
when 'Third_Quarter' then 3
when 'Fourth_quarter' then 4
when 'Spring' then 3
when 'Summer' then 2
when 'Fall' then 4
when 'Winter' then 1
else 5 -- show unexpected input last
end asc
回答by Sree
SELECT Column1, row_number() over(order by substring(column2,280,9))
FROM YourTable
This will give the order byof substring column2.
这将给出order by子字符串 column2。
回答by Chris
You could make the sub string part of the data being selected
您可以使被选择的数据的子字符串部分
SELECT npId, title, URL, issue, SUBSTRING(issue, 4) AS strsort FROM tbl ORDER BY strsort, issue
回答by Taryn East
Yes, you can use if/then constructions anywhere in a sql command.
是的，您可以在 sql 命令的任何位置使用 if/then 结构。
Do you already have a substring-pattern that works for you? if so, you can use something like:
您是否已经有适合您的子字符串模式？如果是这样，您可以使用以下内容：
ORDER BY (IF (SUBSTRING(first_match_of_issue) IS NOT NULL) THEN first_match_of_issue ELSE second_match_of_issue))
However - if you already know what they're going to be, then the CASE statements mentioned in other answers are probably easier.
但是 - 如果您已经知道它们将是什么，那么其他答案中提到的 CASE 语句可能更容易。
