有劳各位大虾了，帮我看看这个排序的问题！！

[xiaoban]

sql="select RTRIM(REPLACE(ywy,' ', '')) as ywy from s_ywjl group by RTRIM(REPLACE(ywy, ' ', '')) order by ywy"
这是sql语句。ywy是个字符型字段，可是里面录入时是可以录入数字的，所以现在我想按数字排序，字符在后，就是这样的效果
1
2
11
222
小明
要怎么改我的sql语句呀？？
cast(ywy as int)
convert(int,ywy)
我已经试过这两种了，都不行，都报“该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中”这个错！！

[haoK]

sql="select RTRIM(REPLACE(ywy,' ', '')) as newywy from s_ywjl group by RTRIM(REPLACE(ywy, ' ', '')) order by newywy"

[lsxaa]

order by case isnumeric(ywy) when 0 then 0 else 1 end,
case isnumeric(ywy) when 0 then cast(ywy as int) else ywy end

[xiaoban]

还是不行呀！！

[lsxaa]

select * from (
select '1' as ywy union all
select '2' as ywy union all
select '11' as ywy union all
select '222' as ywy union all
select '小明' as ywy ) a

order by case isnumeric(ywy) when 1 then 0 else 1 end,
case isnumeric(ywy) when 1 then cast(ywy as int) else 9999999999999 end

[lsxaa]

首先这句话不对
sql="select RTRIM(REPLACE(ywy,' ', '')) as ywy from s_ywjl group by RTRIM(REPLACE(ywy, ' ', '')) order by ywy"
group by 后面的列必须和select 后面的列完全一样才行

sql="select RTRIM(REPLACE(ywy,' ', '')) as ywy from s_ywjl
group by RTRIM(REPLACE(ywy,' ', '')) as ywy order by ywy"

[xiaoban]

不行啊，加了as ywy，就报错了！
要不这样可以实现吗，我不安ywy排序，按三个int型的字段和排序
sql="select RTRIM(REPLACE(ywy,' ', '')) as ywy，sum(num1) as num1,sum(num2) as num2,sum(num3) as num3, from s_ywjl group by RTRIM(REPLACE(ywy, ' ', '')) order by (num1+num2+num3)"
按这三个的和排序，可以吗？？

[zjcxc]

sql="select RTRIM(REPLACE(ywy,' ', '')) as ywy from s_ywjl group by RTRIM(REPLACE(ywy, ' ', '')) order by isnumeric(ywy) desc,ywy"

[xiaoban]

辛苦老大们抽时间帮我拉，可是还是不行呀。我说的那个“按三个int型的字段和排序”这个可以实现吗？？谢谢，，，谢谢！！

[zjcxc]

--1.楼主自己的语句中,select中的空格是半角,而group by中用的是全角,所以要改成统一的
--2.order by 中引用ywy,默认是引用原表的,所以会出现 group by 的问题

--改成下面的就行了

sql="select * from(select RTRIM(REPLACE(ywy,' ', '')) as ywy from s_ywjl group by RTRIM(REPLACE(ywy,' ', '')))a order by isnumeric(ywy) desc,ywy"

[zjcxc]

--还少了一个数字的排序,改成这样:

sql="select * from(select RTRIM(REPLACE(ywy,' ', '')) as ywy from s_ywjl group by RTRIM(REPLACE(ywy,' ', '')))a order by isnumeric(ywy) desc,case when isnumeric(ywy)=1 then cast(ywy as int) end,ywy"

[zjcxc]

--测试数据
create table s_ywjl(ywy varchar(10))
insert s_ywjl select '1'
union all select '2'
union all select '11'
union all select '222'
union all select '小明'
union all select '小 明'
union all select '1小 明'
go

--查询
select * from(select RTRIM(REPLACE(ywy,' ', '')) as ywy from s_ywjl group by RTRIM(REPLACE(ywy,' ', '')))a order by isnumeric(ywy) desc,case when isnumeric(ywy)=1 then cast(ywy as int) end,ywy
go

drop table s_ywjl

/*--测试结果

ywy
-----------------------
1
2
11
222
1小明
小明

（所影响的行数为 6 行）
--*/

[xiaoban]

哇，哈。哈。哈
邹建老大，用你的方法搞定了，太崇拜你拉，高手高手！！万分感谢呀。。。马上结贴