sqlserver中数字排序问题，部分数据首位是字母，求大神指教

悟空2233 2017-12-22 09:50:12

1开始到（N+1，n>=10）的数字排序，直接用order by 显示的是1、11、12、13……101、102、103……2、21、22……n1,n2，n3......n101,n102,n103...n2,n21我想要的是1、2、3、4、5、6……10、11、12、13、……20、21、22、23… n1,n2,n3,n4,n5,n6,......n10,n11,n12,n13,.......n20,n21,n22......按数字的大小排
请大神执教

...全文

619 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

悟空2233 2018-09-19

打赏
举报

后来自己搞定了谢谢各位大佬指教

中国风 2017-12-22

打赏
举报

借用1#测试数据，看效果
e.g.

--rn排序列显示效果

--rn排序列显示效果

;WITH cte AS (

    SELECT '1' as id UNION ALL 

    SELECT '11' UNION ALL 

    SELECT 'n1' UNION ALL 

    SELECT 'n11'UNION ALL 

    SELECT 'n2'

)

SELECT  * ,

        CAST(ASCII(LEFT(id, 1)) AS BIGINT) * 1000000000 + STUFF(id, 1, 1, '')

        * 1 AS rn

FROM    cte

ORDER BY CAST(ASCII(LEFT(id, 1)) AS BIGINT) * 1000000000 + STUFF(id, 1, 1, '')

        * 1 ;

/*

id	rn

1	49000000000

11	49000000001

n1	110000000001

n2	110000000002

n11	110000000011

*/

听雨停了 2017-12-22

打赏
举报

如果只是简单的上面你这样，下面这个逻辑就可以满足



WITH cte AS (

	SELECT '1' as id UNION ALL 

	SELECT '11' UNION ALL 

	SELECT 'n1' UNION ALL 

	SELECT 'n11'UNION ALL 

	SELECT 'n2'

)

SELECT *,cast(REPLACE(id,'n','9') AS bigint) AS rn FROM cte ORDER BY rn

吉普赛的歌 2017-12-22

打赏
举报

;WITH cte(id) AS (
	SELECT '1'
	UNION ALL
	SELECT '11'
	UNION ALL
	SELECT '12'
	UNION ALL
	SELECT '13'
	UNION ALL
	SELECT '101'
	UNION ALL
	SELECT '102'
	UNION ALL
	SELECT '103'
	UNION ALL
	SELECT '2'
	UNION ALL
	SELECT '21'
	UNION ALL
	SELECT '22'
	UNION ALL
	SELECT 'n1'
	UNION ALL
	SELECT 'n2'
	UNION ALL
	SELECT 'n3'
	UNION ALL
	SELECT 'n101'
	UNION ALL
	SELECT 'n102'
	UNION ALL
	SELECT 'n103'
	UNION ALL
	SELECT 'n2'
	UNION ALL
	SELECT 'n21'
)
SELECT * FROM cte
ORDER BY 
	ISNUMERIC(LEFT(id,1)) DESc,CAST(REPLACE(id,'n','') AS INT)
/*
id
1
2
11
12
13
21
22
101
102
103
n1
n2
n2
n3
n21
n101
n102
n103
*/

薛定谔的DBA 2017-12-22

打赏
举报

WITH tab AS (
    SELECT '1' as id UNION ALL 
    SELECT '11' UNION ALL 
	SELECT '101' UNION ALL 
    SELECT '2' UNION ALL 
	SELECT '201' UNION ALL 
    SELECT 'n1' UNION ALL 
    SELECT 'n11'UNION ALL 
    SELECT 'n2' UNION ALL 
    SELECT 'n101'
)
SELECT * FROM tab ORDER BY (CASE WHEN ISNUMERIC(id)=1 THEN CONVERT(INT,id) ELSE 999999 END),id

早起晚睡 2017-12-22

打赏
举报

如果你都是数字的话

 WITH cte AS (
    SELECT '1' as id UNION ALL 
    SELECT '11' UNION ALL 
    SELECT '21' UNION ALL 
    SELECT '211'UNION ALL 
    SELECT '102')
    SELECT *,cast(id AS bigint) AS rn FROM cte ORDER BY rn

借用下楼上的代码