SQL:在特定列中使用子字符串进行ORDER BY ...可能吗? [英] SQL: ORDER BY using a substring within a specific column... possible?

问题描述

我有一个数据库，其列为npID，标题，URL和问题.

I have a database whose columns are npID, title, URL, and issue.

以下是输入两年的示例:

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

我想做的是能够根据问题"列中的子字符串对结果进行排序.但是，直到2010年，客户才使用季节作为标题，而在2010年，他们开始使用季度.如果在问题"值中的任何位置找到了单词，"ORDER BY"中是否可以提供一个单词列表进行排序?

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?

我希望最终结果是这样的:

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

推荐答案

您可以在ORDER BY中放入CASE语句来完成此操作.更好的方法是更改​​应用程序和表，以便在有开发时间时将相关数据实际存储在它所属的列中.

You can put a CASE statement in the ORDER BY to 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.

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

根据需要订购季节.您还可以通过调整CASE语句以特定的方式对它们进行排序(Q1，然后是Spring，然后是Q2，等等).

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 CASE statement.

这篇关于SQL:在特定列中使用子字符串进行ORDER BY ...可能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！