如何在这样的sql中订购.. [英] How to order by in sql like this..

问题描述

我有一张桌子，它的价值是这样的

区域subArea ID Ac 
 ------ -------------------------------- 
 ARRAH ARA URBAN-1 001 9027 
 ARRAH ARA URBAN -1 001 E-2399 
 ARRAH ARA URBAN-1 003 E-2402 
 ARRAH ARA URBAN-1 003 E-2403 
 ARRAH ARA URBAN-1 003 E-3289 
 ARRAH ARA URBAN-1 001 E-2936 
 ARRAH ARA URBAN-1 001 E-2937 
 ARRAH ARA URBAN-1 001 E-2794 
 ARRAH ARA URBAN-1 005 E-2803 
 ARRAH ARA URBAN-1 005 H-1173 
 ARRAH ARA URBAN-1 005 H-1174 
 ARRAH ARA URBAN-1 005 H-02660 
 ARRAH ARA URBAN-1 001 H-02591 
 ARRAH ARA URBAN-1 001 H-02595 
 ARRAH ARA URBAN-1 001 H-05465 
 ARRAH ARA URBAN-1 002 H-03756 
 ARRAH ARA URBAN-1 002 H- 5482 

我想通过Area，subArea，ID，Ac来订购。

i主要通过以下方式订购像这样

<前lang =text> 9027
E-2399
E-2402
E-2403
E-2794
E-2803
E-2936
E-2937
E-3289
H-11 73
H-1174
H-02591
H-02595
H-02660
H-03756
H-05465
H-5482

请帮帮我..

谢谢..



[edit]已添加代码块 - OriginalGriff [/ edit]

解决方案

这有点复杂，因为您正在尝试按顺序排序字符串值一个数值 - 所以你需要做的是将数字部分转换为整数：

  SELECT  *  FROM  MyTable 
  ORDER   BY  
（ CASE   WHEN  SUBSTRING（Ac， 1 ， 1 ） LIKE  '  [0-9]' 
 那么 CAST（Ac  AS   INT ）
  ELSE  CAST（SUBSTRING（Ac，< span class =code-digit> 3 ， 999 ） AS   INT ）+  1000000  
  END ）

1000000附加组件是将所有E-和H-值远远超出仅数字值。



您可能需要稍微更改它以使用AC字段的第一个字符来修改1000000偏移量，如果您还想要包含在排序中，或者提供两个Order子句。

我猜这就是你想要做的。字段和选择是独立的。

 选择区域，子区域，ID ，AC 来自 mytable 订单  by  AC 

 SELECT * FROM TABLE按AC排序

I have a table and its value like this

Area    subArea         ID       Ac
--------------------------------------
ARRAH	ARA URBAN-1	001	9027
ARRAH	ARA URBAN-1	001	E-2399
ARRAH	ARA URBAN-1	003	E-2402
ARRAH	ARA URBAN-1	003	E-2403
ARRAH	ARA URBAN-1	003	E-3289
ARRAH	ARA URBAN-1	001	E-2936
ARRAH	ARA URBAN-1	001	E-2937
ARRAH	ARA URBAN-1	001	E-2794
ARRAH	ARA URBAN-1	005	E-2803
ARRAH	ARA URBAN-1	005	H-1173
ARRAH	ARA URBAN-1	005	H-1174
ARRAH	ARA URBAN-1	005	H-02660
ARRAH	ARA URBAN-1	001	H-02591
ARRAH	ARA URBAN-1	001	H-02595
ARRAH	ARA URBAN-1	001	H-05465
ARRAH	ARA URBAN-1	002	H-03756
ARRAH	ARA URBAN-1	002	H-5482

I want order it by Area,subArea,ID,Ac.
i want mainly order it by Ac like this

9027
E-2399
E-2402
E-2403
E-2794
E-2803
E-2936
E-2937
E-3289
H-1173
H-1174
H-02591
H-02595
H-02660
H-03756
H-05465
H-5482

please help me..
Thanks..

[edit]Code block added - OriginalGriff[/edit]

解决方案

This is a little complex, because you are trying to order a string value by a numeric value - so what you need to do is cast the numeric part as an integer:

SELECT * FROM MyTable
ORDER BY
(CASE WHEN SUBSTRING(Ac, 1, 1) LIKE '[0-9]'
         THEN CAST(Ac AS INT)
      ELSE CAST(SUBSTRING(Ac, 3, 999) AS INT) + 1000000
 END)

The 1000000 add-on is to move all the "E-" and "H-" values far beyond the "numeric only" values.

You may want to change it slightly to use the first character of the AC field to modify the 1000000 offset if you also want that included in the ordering, or provide two Order clauses.

I guess this what you want to do. fields and select are independent.

select Area,subArea,ID,AC from mytable order by AC

SELECT * FROM TABLE Order By AC

这篇关于如何在这样的sql中订购..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！