如何按数字排序列表，然后按字符串递减排序？

I have a list of data that I would like to order descending but i would like to start out with numbers then go to strings.
Example of what I'm looking for.

我有一个数据列表,我想订购降序但我想从数字开始然后转到字符串。我正在寻找的例子。

43a
34a
22a
11d
ztc
xtc
ytc

but if you just do

但如果你这样做

SELECT list FROM table ORDER BY list DESC;

It outputs the list like this.

它输出这样的列表。

ztc
xtc
ytc
43a
34a
22a
11d

Is there a way to do this?

有没有办法做到这一点?

3 个解决方案

#1

Test table

CREATE TABLE Data (str varchar(3))

INSERT INTO Data
SELECT '43a' UNION
SELECT '34a' UNION
SELECT '22a' UNION
SELECT '11d' UNION
SELECT 'ztc' UNION
SELECT 'xtc' UNION
SELECT 'ytc'

TSQL

SELECT str FROM DATA
ORDER BY 1-ISNUMERIC(LEFT(str, 1)) , str DESC

ISNUMERIC return 1 for a number, 0 for non-number, so if u sort by 1-ISNUMERIC first - numbers appear first. After that you can sort by field DESC

ISNUMERIC返回1表示数字,0表示非数字,因此如果您按1-ISNUMERIC排序,则首先显示数字。之后,您可以按字段DESC排序

Demo: http://sqlfiddle.com/#!3/e92a08/6

#2

Something like this:

像这样的东西:

;WITH CTE
AS
(
SELECT A FROM (VALUES('ztc'), ('xtc'), ('ytc'),('43a'),('34a'),('22a'),('11d')) T(A)
)
SELECT * FROM CTE
ORDER BY Left(A, PATINDEX('%[a-Z]%', A) - 1) DESC

#3

You can split it into two queries a temporary table.

您可以将其拆分为两个查询临时表。

DECLARE @TempList AS TABLE(
     listItem AS varchar(max)
)

In the first query, you can use:

在第一个查询中,您可以使用:

INSERT INTO @TempList (listItem)
SELECT list 
FROM table 
Where ISNUMERIC(SUBSTRING(LTRIM(list), 1, 1))
ORDER BY list DESC;

What this will do is select all entries that start with a number and add them to the list in descending order.

这将做的是选择所有以数字开头的条目,并按降序将它们添加到列表中。

Then, you just remove the 'NOT' from the WHERE statement the letters:

然后,您只需从WHERE语句中删除'NOT'字母:

Insert into @TempList
SELECT list 
FROM table 
Where Not ISNUMERIC(SUBSTRING(LTRIM(list), 1, 1))
ORDER BY list DESC;

Finally, to get the full list you just use:

最后,要获得您刚刚使用的完整列表:

SELECT listItem FROM @TempList

#1