一条查询语句 疑难～～标题要长

[enderboy520]

我想有一条语句达到以下效果
如何使用ORDER BY或者别的什么？

A

B

C

D

1

2

3

4

5

6

7

8

9

10

11

12

13

14

创建表语句如下
create table #data (data varchar(10))

insert into #data

select '1' data union all

select '2' data union all

select '3' data union all

select '4' data union all

select '5' data union all

select '6' data union all

select '7' data union all

select '8' data union all

select '9' data union all

select '10' data union all

select '11' data union all

select '12' data union all

select '13' data union all

select '14' data union all

select 'A' data union all

select 'B' data union all

select 'C' data union all

select 'D' data





select * from #data

[情殇无限]

[Quote=引用 4 楼 haiwer 的回复:]

1楼不够严谨
[/Quote]

select * from #data order by 
case when isnumeric(data) = 1 then 1 else 0 end,case when isnumeric(data) = 1 then cast(data as int)
else data end

3楼不够严谨，出现负数就不对了

[enderboy520]

SELECT * FROM data ORDER BY ISNUMERIC(data), RIGHT('00000' + CONVERT(varchar, data), 5)
这样如何 有高人指教没

[永生天地]

[Quote=引用 13 楼 enderboy520 的回复:]
SELECT * FROM data ORDER BY ISNUMERIC(data), RIGHT('00000' + CONVERT(varchar, data), 5)
这样如何 有高人指教没
[/Quote]
我是用这样的方式写的，看上去可行

[东那个升]

[Quote=引用 12 楼 shutao917 的回复:]
引用 4 楼 haiwer 的回复:

1楼不够严谨


SQL code
select * from #data order by
case when isnumeric(data) = 1 then 1 else 0 end,case when isnumeric(data) = 1 then cast(data as int)
else data end


3楼不够……
[/Quote]

select * 
,case when  isnumeric(data) = 1 then CAST(data AS int) end  
,case when  isnumeric(data) = 0 then data end 
from #data
order by  isnumeric(data)

data                   
---------- ----------- ----------
A          NULL        A
B          NULL        B
C          NULL        C
D          NULL        D
-1         -1          NULL
-2         -2          NULL
-3         -3          NULL
1          1           NULL
2          2           NULL
3          3           NULL
4          4           NULL
5          5           NULL
6          6           NULL
7          7           NULL
8          8           NULL
9          9           NULL
10         10          NULL
11         11          NULL
12         12          NULL
13         13          NULL
14         14          NULL

(21 行受影响)


select * 
from #data
order by  isnumeric(data)
,case when  isnumeric(data) = 1 then CAST(data AS int) end  
,case when  isnumeric(data) = 0 then data end 

data
----------
A
B
C
D
-3
-2
-1
1
2
3
4
5
6
7
8
9
10
11
12
13
14

(21 行受影响)

加点负的测试测试

[水族杰纶]

create table #data (data varchar(10))
insert into #data
select '1' data union all
select '2' data union all
select '3' data union all
select '4' data union all
select '5' data union all
select '6' data union all
select '7' data union all
select '8' data union all
select '9' data union all
select '10' data union all
select '11' data union all
select '12' data union all
select '13' data union all
select '14' data union all
select 'A' data union all
select 'B' data union all
select 'C' data union all
select 'D' data
select *
from #data
order by case when isnumeric(data)=0 then 1 else 2 end
drop table #data
/*
data
----------
A
B
C
D
1
2
3
4
5
6
7
8
9
10
11
12
13
14
*/

[enderboy520]

[Quote=引用 1 楼 wufeng4552 的回复:]

SQL code
create table #data (data varchar(10))
insert into #data
select '1' data union all
select '2' data union all
select '3' data union all
select '4' data union all
select '5' data union all
sel……
[/Quote]

楼上的你想做咩？
这沙发你也抢？

[昵称被占用了]

select * from #data
order by case when isnumeric(data) = 1 then cast(data as int) else 0 end,data

[昵称被占用了]

1楼不够严谨

[王向飞]

敲代码的速度胜过我抢沙发的速度。

[情殇无限]

select * from #data
order by case when isnumeric(data) = 1 then cast(data as int) else 0 end,data

[enderboy520]

[Quote=引用 3 楼 haiwer 的回复:]

SQL code
select * from #data
order by case when isnumeric(data) = 1 then cast(data as int) else 0 end,data
[/Quote]

原来是藏起来了

[enderboy520]

[Quote=引用 4 楼 haiwer 的回复:]

1楼不够严谨
[/Quote]


哥有啥好想法列？
能帮我看一下么

[东那个升]

会有字母数字的混合体么

[enderboy520]

[Quote=引用 9 楼 ldslove 的回复:]

会有字母数字的混合体么
[/Quote]
单纯的字符

现在只有单字节

[永生天地]

试试吧，也许可行

select * from #data
order by isnumeric(data),right(space(20)+data,20)


/*
data
----------
A
B
C
D
1
2
3
4
5
6
7
8
9
10
11
12
13
14

(18 行受影响)

*/