以下是按order by fmodel,能否做到让所有“Y-”开头的排在“Y2-”开头的前面,sql2000fmodel fqty
Y-0.55-4 7
Y-18.5-2 1
Y-18.5-4 5
Y-2.2-2 12
Y-2.2-4 61
Y-2.2-6 24
Y2-0.18-4 1
Y2-0.25-2 3
Y2-0.25-6 4
Y2-0.37-4 8
Y2-2.2-2 5
Y2-2.2-4 4
Y2-200-6 2
Y-22-2 2
Y2-220-6 3
Y-22-4 8
Y2-250-6 2
Y-22-6 4
Y2-3.3-4 48
Y2-30-4 10
Y2-3-2 5
Y2-3-4 11
Y2-34-4 4
Y2-4-2 10
Y2-4-4 3
Y2-55-4 1
Y-5.5-4 46
Y-5.5-6 1
Y-55-2 2
Y-55-6 2
Y-7.5-2 11
Y-7.5-4 70
Y-7.5-6 8
Y-0.55-4 7
Y-18.5-2 1
Y-18.5-4 5
Y-2.2-2 12
Y-2.2-4 61
Y-2.2-6 24
Y2-0.18-4 1
Y2-0.25-2 3
Y2-0.25-6 4
Y2-0.37-4 8
Y2-2.2-2 5
Y2-2.2-4 4
Y2-200-6 2
Y-22-2 2
Y2-220-6 3
Y-22-4 8
Y2-250-6 2
Y-22-6 4
Y2-3.3-4 48
Y2-30-4 10
Y2-3-2 5
Y2-3-4 11
Y2-34-4 4
Y2-4-2 10
Y2-4-4 3
Y2-55-4 1
Y-5.5-4 46
Y-5.5-6 1
Y-55-2 2
Y-55-6 2
Y-7.5-2 11
Y-7.5-4 70
Y-7.5-6 8
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([fmodel] varchar(9),[fqty] int)
insert [TB]
select 'Y2-0.55-4',7 union all
select 'Y2-18.5-2',1 union all
select 'Y2-18.5-4',5 union all
select 'Y-22.2-2',12 union all
select 'Y-2.2-4',61 union all
select 'Y-2.2-6',24 union all
select 'Y2-0.18-4',1 union all
select 'Y2-0.25-2',3 union all
select 'Y2-0.25-6',4
GO--> 查询结果
SELECT * FROM [TB]
order by left(fmodel,2),fmodel
--> 删除表格
--DROP TABLE [TB]
order by left(fmodel,2),fmodel
(
select 'Y-0.55-4' fmodel,7 fqty
union select 'Y-18.5-2',1
union select 'Y-18.5-4',5
union select'Y2-0.18-4',1
union select'Y2-0.25-2',3
union select 'Y-2.2-4',61
union select 'Y-2.2-6',24
) as t
order by fmodel