在SQL查询中按升序排序 [英] Sorting in SQL query in ascending order

问题描述

我在表格中有一个varchar字段CaseNo，其中包含此格式的数据，例如：

FA / 12/2014，FA / 11/2015，FA / 12/2015， FA / 11/2014，CC / 12/2015，CC / 11/2015 。

我想按以下方式对选择查询的结果进行排序



CC / 11/2015

CC / 12/2015

FA / 11/2014

FA / 12/2014

FA / 11/2015

FA / 12/2015



首先它应先排序按字母顺序排列的两个字符。然后按升序排列的剩余数字和结果应如上所示..

这样做可以。

谢谢

I have a varchar field CaseNo in table which contains the data in this format for eg.
FA/12/2014, FA/11/2015, FA/12/2015, FA/11/2014, CC/12/2015, CC/11/2015.
I wanted to sort the result of select query in the following manner

CC/11/2015
CC/12/2015
FA/11/2014
FA/12/2014
FA/11/2015
FA/12/2015

Firstly it should sort first two characters in alphabetic order. And Then the remaining digits in ascending order and result should be like as above..
Is this possible to do so.
Thanks

推荐答案

不要。

有可能，有很多工作和临时表：将列中的逗号分隔数据转换为行以供选择 [ ^ ]通过拆分每个条目开始到一个单独的行。但这是一个糟糕的主意：你应该为每个值使用一个单独的表，并将一个外键返回到相关的行。然后将其存储在单独的字段中，这样您的排序就变得微不足道了。

如果要显示它，则将列一起添加以生成要显示的数据，并使用JOIN将数据与其父行重新组合。

Don't.
It's possible, with a lot of work and a temporary table: Converting comma separated data in a column to rows for selection[^] gives you a start by splitting each entry to a separate row. But it's a poor idea: you should really be using a separate table for each value, with a foreign key back to the relevant row. And then store it in separate fields so your sorting becomes trivial.
When you want to display it, you then add the columns together to produce the data for display and use a JOIN to "reassemble" the data with it's parent row.

您可以使用以下查询来获得所需的结果。

You can use below query to get desired result.

SELECT columnName  FROM TableName
ORDER BY SUBSTRING(columnName,0,3) ,SUBSTRING(columnName,7,4), SUBSTRING(columnName,4,7)

注意：如果你有很多表中的行，可能会对性能产生一些影响。在这种情况下，您可以将值保留在自动计算列中并对其应用排序。

Note: If you have lots of rows in table, it might have some performance impact. In that case you can keep values in auto calculated columns and apply sorting on that.

正如OriginalGriff指出的那样，永远不要在单个列中存储多个值。这样做会在你的程序中引入一堆不必要的复杂性，例如你目前要问的问题。



如果你有三列，你可以很容易将排序定义为

As OriginalGriff pointed out, never store several values in a single column. Doing so will introduce a whole bunch of unnecessary complexity in your program, for example the question you're currently asking.

If you would have three columns, you could easily define the sorting as

ORDER BY Col1, Col2, Col3

就是这样。完全没有复杂性。



如果由于某种原因，你需要数据库能够显示连接在一起的三个字段，你可以随时在选择中声明，使用视图或创建计算列。

And that's it. No complexity at all.

If, for some reason, you need the database to be able to show the three fields concatenated together, you can always so it in the select statement, use a view or create a computed column.

这篇关于在SQL查询中按升序排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！