字段是文本类型latout1
latout2
latout3
latout4
latout10order by 后会变成latout1
latout10
latout2
latout3
latout4我想要
latout1
latout2
latout3
latout4
latout10
这样排序,该怎样忘记了。。
latout2
latout3
latout4
latout10order by 后会变成latout1
latout10
latout2
latout3
latout4我想要
latout1
latout2
latout3
latout4
latout10
这样排序,该怎样忘记了。。
latout1
latout2
latout3
latout4 都要排在前四位。要怎样写?
insert into tb values(N'latout1')
insert into tb values(N'latout2')
insert into tb values(N'latout3')
insert into tb values(N'latout4')
insert into tb values(N'latout10')
goselect * from tb order by cast(replace(cast(col as varchar),'latout','') as int)
drop table tb /*
col
------------------------
latout1
latout2
latout3
latout4
latout10(所影响的行数为 5 行)*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-28 17:23:42
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(8))
insert [tb]
select 'latout1' union all
select 'latout2' union all
select 'latout3' union all
select 'latout4' union all
select 'latout7' union all
select 'latout5' union all
select 'Latout6' union all
select 'latout10'
--------------开始查询--------------------------
select * from [tb] order by cast(replace(cast(col as varchar),'latout','') as int)
----------------结果----------------------------
/* col
--------
latout1
latout2
latout3
latout4
latout5
Latout6
latout7
latout10(8 行受影响)
*/
.Append(" FROM GZTLayOut AS L LEFT JOIN GZTLayOutSub AS Z ON L.L_Id = Z.Z_LId")
.Append(" WHERE L_Default='Y'or 'n' ORDER BY cast(replace(cast(L.L_LayOutName as varchar),'latout','') as int) "); DataTable dt = DataAccess.getTB(sb.ToString());
FROM tb
ORDER BY cint(mid(L.L_LayOutName,LEN('latout')+1));
declare @t table([col] varchar(8))
insert @tselect 'latout2' union all
select 'latout1' union all
select 'latout3' union all
select 'latout4' union all
select 'latout7' union all
select 'latout5' union all
select 'Latout6' union all
select 'latout10'select *
from @t
order by len(col),colcol
--------
latout1
latout2
latout3
latout4
latout5
Latout6
latout7
latout10(所影响的行数为 8 行) 这样行不?