关于sql 排序204-1-1、204-1-11、204-1-2怎么排序成204-1-1、204-1-2、204-1-11
解决方案 »
- 同字段下的日期计算
- 执行查询遇到的问题
- 如何不重复的随即关联两张表的ID
- 紧急求助:关于sql server 2000数据库安全的问题
- 看过SQL SERVER 2008从入门到精通(第三版)的朋友请进来!!
- 扫描碎片发现如下信息,请问这个是好的不,。正常么
- 如何使用客户端的事件探查器跟踪服务器端的活动?
- 快来拿分一个SQL SERVER2000 标准版的安装问题!、、、、、100分
- 请问?外键是????
- SQL server7触发器的问题,是否存在不触发的BUG?如何测试?
- 求大神把SQLSERVER索引转换成ORACLE索引
- sql server C#一个一边读取一边删除的问题,数据被锁住了
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-06-03 11:25:56
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(8))
insert [tb]
select '204-1-1' union all
select '204-1-11' union all
select '204-1-2'
--------------开始查询--------------------------
select * from [tb] order by cast(replace(col,'-','') as int)
----------------结果----------------------------
/* col
--------
204-1-1
204-1-2
204-1-11(3 行受影响)*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-06-03 11:25:56
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(80))
insert [tb]
select '204-1-1' union all
select '204-1-11' union all
select '204-1-2' union all
select '新204-1-12'
--------------开始查询--------------------------
select * from [tb] order by case when IsNumeric(replace(col,'-',''))=1 then 0 else 1 end
----------------结果----------------------------
/*col
--------------------------------------------------------------------------------
204-1-1
204-1-11
204-1-2
新204-1-12(4 行受影响)*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-06-03 11:25:56
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(80))
insert [tb]
select '204-1-1' union all
select '204-1-11' union all
select '204-1-2' union all
select '新204-1-12'
--------------开始查询--------------------------
select
col
from
(select case when IsNumeric(replace(col,'-',''))=1 then cast(replace(col,'-','') as int) else 1000000000000000000 end as px,* from [tb])t
order by
px,col ----------------结果----------------------------
/*col
--------------------------------------------------------------------------------
204-1-1
204-1-2
204-1-11
新204-1-12
*/