在SQL Server中排序列 [英] Sort Column in SQL Server
问题描述
大家好，
我在sql server 2008 R2中有一个表，其价值如
1.0.0.0.0
2.0.0.0.0
3.0.0.0.0
4.0.0.0.0
5.0.0.0.0
6.0.0.0.0
7.0.0.0.0
8.0.0.0.0
9.0.0.0.0
10.0.0.0.0
11.0.0.0.0
12.0.0.0。 0
现在，当我对此专栏进行排序时，那段时间给我的结果如
1.0 .0.0.0
10.0.0.0.0
11.0.0.0.0
12.0.0.0.0
2.0.0.0.0
3.0.0.0.0
4.0.0.0.0
5.0.0.0.0
6.0.0.0.0
7.0.0.0.0
8.0.0.0.0
9.0.0.0.0
哪个错了。作为varchar字段中的这一列，它给出了如上所示的输出，
所以如何正确排序数据。（任何转换，拆分文本然后处理？）
全部谢谢
我们不能使用ABS（）直接在Column Name上，因为它无法转换为Decimal或int ...
选择 ColumnName From TableName 订单 按 ABS（左（ColumnName，CharIndex（' 。'，ColumnName）））
- Abs将始终返回任意数字的正值
- 或
选择 ColumnName From TableName
订单 按 转换（ Real ，左（ColumnName，CharIndex（' 。'，ColumnName）））
Raja的解决方案2非常好。另一种方法是使用 CTE 将数字拆分成列[ ^ ]和 PIVOT [ ^ ]：
- 声明临时表
DECLARE @ tmp TABLE （MyNumber VARCHAR （ 30 ））
- 插入数据
INSERT INTO @ tm p （MyNumber）
SELECT ' 1.0.0.0.0'
UNION ALL SELECT ' 10.0.0.0.0'
UNION ALL SELECT ' 11.0.0.0.0'
UNION < span class =code-keyword> ALL SELECT ' 12.0.0.0.0'
UNION 所有 SELECT ' 2.0.0.0.0'
UNION ALL SELECT ' 3.0.0.0.0'
UNION ALL < span class =code-keyword> SELECT ' 4.0.0.0.0'
UNION ALL SELECT ' 5.0.0.0.0'
UNION ALL SELECT ' 6.0.0.0.0'
UNION 所有 SELECT ' 7.0.0.0.0'
UNI ON ALL SELECT ' 8.0.0.0.0'
UNION ALL SELECT ' 9.0.0.0.0'
- 获取零件
; WITH 部件 AS
（
- 初始部分（获取第一个数字）
SELECT 1 AS PartNo， CONVERT （ INT ， LEFT （MyNumber，CHARINDEX（' '。，MyNumber）-1）） AS 编号， RIGHT （MyNumber，LEN（MyNumber） - CHARINDEX （' 。'，MyNumber）） AS 剩余
FROM @ tmp
WHERE CHARINDEX（' 。'，MyNumber）> 0
< span class =code-keyword> UNION ALL
- < span class =code-comment>递归部分（获取第二，第三......数字）
SELECT PartNo + 1 AS PartNo， CONVERT （ INT ， LEFT （剩余，CHARINDEX（' 。，剩余）-1）） AS 数字， RIGHT （剩余，LEN（剩余） - CHARINDEX（' 。'，Remainder）） AS 剩余
FROM 零件
WHERE CHARINDEX（ ' 。'，余数）> 0
UNION ALL
SELECT PartNo + 1 AS PartNo， CONVERT （ INT ，剩余） AS 编号， NULL AS 剩余
FROM 部件
WHERE CHARINDEX（' 。'，Remainder）= 0
）
- 定义数据透视字段
SELECT [RowNo]，[ 1 ]，[ 2 ]，[ 3 ]，[ 4 ]，[ 5 ]
FROM （
- 源表
SELECT ROW_NUMBER（） OVER （ PARTITION BY [PartNo] ORDER BY [PartNo]）< span class =code-keyword> AS RowNo，PartNo，Number
FROM 零件
） AS DT
PIVO T（MAX（[Number]） FOR [PartNo] IN （[ 1 ]，[ 2 ]，[ 3 ]，[ 4 ]，[ 5 ]）） AS UNPVT
ORDER BY [ 1 ] ，[ 2 ]，[ 3 ]，[ 4 ]，[ 5 ]
输出：
[RowNo] [1] [2] [2] [4] [5]
1 1 0 0 0 0
5 2 0 0 0 0
6 < span class =code-leadattribute> 3 0 0 0 0
7 4 0 0 0 0
8 5 0 0 0 0
9 6 0 0 0 0
10 7 0 0 0 0
11 8 0 0 0 0
12 9 0 0 0 0
2 10 0 0 0 0
3 11 0 0 0 0
4 12 0 0 0 0
尝试在您的值上使用 ABS（），请参阅此处： http://v3.thewatchmakerproject.com/journal/231/ [<一个href =http://v3.thewatchmakerproject.com/journal/231/target =_ blanktitle =新窗口> ^ ]
Hi All,
I have one table in sql server 2008 R2, which is having values like
1.0.0.0.0
2.0.0.0.0
3.0.0.0.0
4.0.0.0.0
5.0.0.0.0
6.0.0.0.0
7.0.0.0.0
8.0.0.0.0
9.0.0.0.0
10.0.0.0.0
11.0.0.0.0
12.0.0.0.0
Now when i apply sorting to this column then that time is giving me result like
1.0.0.0.0
10.0.0.0.0
11.0.0.0.0
12.0.0.0.0
2.0.0.0.0
3.0.0.0.0
4.0.0.0.0
5.0.0.0.0
6.0.0.0.0
7.0.0.0.0
8.0.0.0.0
9.0.0.0.0
which is wrong. as this column in varchar field, it is giving the output like above,
so how can proper sorted data.(any Conversion, Splitting text and then handle ?)
Thanks All
We can't useABS()Directly on the Column Name as it cannot be converted to Decimal or int...
Select ColumnName From TableName Order by ABS(Left(ColumnName,CharIndex('.',ColumnName))) -- Abs will always Return positive value of any number --or Select ColumnName From TableName Order by Convert(Real,Left(ColumnName,CharIndex('.',ColumnName)))
Solution 2 by Raja is very good. Another way is to split numbers into columns using CTE[^] and PIVOT[^]:
--declare temporary table DECLARE @tmp TABLE (MyNumber VARCHAR(30)) --insert data INSERT INTO @tmp (MyNumber) SELECT '1.0.0.0.0' UNION ALL SELECT '10.0.0.0.0' UNION ALL SELECT '11.0.0.0.0' UNION ALL SELECT '12.0.0.0.0' UNION ALL SELECT '2.0.0.0.0' UNION ALL SELECT '3.0.0.0.0' UNION ALL SELECT '4.0.0.0.0' UNION ALL SELECT '5.0.0.0.0' UNION ALL SELECT '6.0.0.0.0' UNION ALL SELECT '7.0.0.0.0' UNION ALL SELECT '8.0.0.0.0' UNION ALL SELECT '9.0.0.0.0' --get the parts ;WITH Parts AS ( --initial part (get first number) SELECT 1 AS PartNo, CONVERT(INT,LEFT(MyNumber, CHARINDEX('.',MyNumber)-1)) AS Number, RIGHT(MyNumber, LEN(MyNumber) - CHARINDEX('.',MyNumber)) AS Remainder FROM @tmp WHERE CHARINDEX('.',MyNumber)>0 UNION ALL --recursive part (get second, third... number) SELECT PartNo+1 AS PartNo, CONVERT(INT,LEFT(Remainder, CHARINDEX('.',Remainder)-1)) AS Number, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('.',Remainder)) AS Remainder FROM Parts WHERE CHARINDEX('.',Remainder)>0 UNION ALL SELECT PartNo+1 AS PartNo, CONVERT(INT,Remainder) AS Number, NULL AS Remainder FROM Parts WHERE CHARINDEX('.',Remainder)=0 ) --define pivot fields SELECT [RowNo], [1], [2], [3], [4], [5] FROM ( --source table SELECT ROW_NUMBER() OVER(PARTITION BY [PartNo] ORDER BY [PartNo]) AS RowNo, PartNo, Number FROM Parts ) AS DT PIVOT(MAX([Number]) FOR [PartNo] IN ([1], [2], [3], [4], [5])) AS UNPVT ORDER BY [1], [2], [3], [4], [5]
Output:
[RowNo] [1] [2] [2] [4] [5] 1 1 0 0 0 0 5 2 0 0 0 0 6 3 0 0 0 0 7 4 0 0 0 0 8 5 0 0 0 0 9 6 0 0 0 0 10 7 0 0 0 0 11 8 0 0 0 0 12 9 0 0 0 0 2 10 0 0 0 0 3 11 0 0 0 0 4 12 0 0 0 0
Try using ABS() on your values, see here : http://v3.thewatchmakerproject.com/journal/231/[^]这篇关于在SQL Server中排序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
