22,209
社区成员
发帖
与我相关
我的任务
分享
select * from [table] order by 代码T
create table [table](名称N varchar(10),代码T varchar(10),顺序S int)
insert into [table] values('纱' , '12' , 1)
insert into [table] values('棉' , '13' , 2)
insert into [table] values('绦纱' , '122d' , 1)
insert into [table] values('布纱' , '12b6' , 2)
insert into [table] values('氯绦纱', '122dee', 1)
insert into [table] values('氨绦纱', '122dfa', 2)
insert into [table] values('纯棉' , '13e9' , 1)
go
select * from [table] order by cast(代码T as varchar)
drop table [table]
/*
名称N 代码T 顺序S
---------- ---------- -----------
纱 12 1
绦纱 122d 1
氯绦纱 122dee 1
氨绦纱 122dfa 2
布纱 12b6 2
棉 13 2
纯棉 13e9 1
(所影响的行数为 7 行)
*/
if object_id('[tb]') is not null drop table [tb]
create table [tb] (名称N varchar(6),代码T varchar(6),顺序S int)
insert into [tb]
select '纱','12',1 union all
select '棉','13',2 union all
select '绦纱','122d',1 union all
select '布纱','12b6',2 union all
select '氯绦纱','122dee',1 union all
select '氨绦纱','122dfa',2 union all
select '纯棉','13e9',1
select * from [tb] order by right(名称N,1) desc,
case when right(名称N,1)='纱' then 顺序S end,
case when right(名称N,1)='棉' then 顺序S end desc
drop table [tb]
/*
纱 12 1
绦纱 122d 1
氯绦纱 122dee 1
氨绦纱 122dfa 2
布纱 12b6 2
棉 13 2
纯棉 13e9 1
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-09-08 10:26:37
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([名称N] varchar(6),[代码T] varchar(6),[顺序S] int)
insert [tb]
select '纱','12',1 union all
select '棉','13',2 union all
select '绦纱','122d',1 union all
select '布纱','12b6',2 union all
select '氯绦纱','122dee',1 union all
select '氨绦纱','122dfa',2 union all
select '纯棉','13e9',1
--------------开始查询--------------------------
select * from tb order by patindex('%[0-9]%',[代码T]),代码T
----------------结果----------------------------
/* 名称N 代码T 顺序S
------ ------ -----------
纱 12 1
绦纱 122d 1
氯绦纱 122dee 1
氨绦纱 122dfa 2
布纱 12b6 2
棉 13 2
纯棉 13e9 1
(7 行受影响)
*/