直接 order by 列
解决方案 »
- 2个表的问题。。。。。。
- SQL简单的分页问题
- 如何防止表内被插入重复记录
- 这条SQL为什么错误,select Quqntity * Price AS Amount,Tax * Amount AS EndAmount From Sale
- access转成sqlserver后其中一字段取不出值的奇怪问题
- 请问如何用SQL语句分类统计某个属性的null值和不为null的记录数?
- 急问:SQL SERVER AGENT无法启动怎么办?
- SQL Server无人值守安装指南
- 如何利用开发工具来倍份SQL SERVER数据库?用pb,vc举例谢谢帮忙
- 各位在设计数据库的时候,外键关系建不建啊?
- CASE语句问题
- 求一个高效的存储过程编写
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-11-21 10:24:35
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](col varchar(20))
insert [tb]
select '1a' union all
select '2' union all
select '2a' union all
select '4a' union all
select '4b' union all
select '4' union all
select '5a' union all
select '5'
--------------开始查询--------------------------
select * from [tb] ORDER BY LEFT(col,PATINDEX('%[a-z][A-Z]%',col)+1),SUBSTRING(col,PATINDEX('%[a-z][A-Z]%',col),LEN(col))
----------------结果----------------------------
/* col
--------------------
1a
2
2a
4
4a
4b
5
5a(8 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-11-21 10:31:56
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(4))
insert [tb]
select '1' union all
select '10' union all
select '100' union all
select '1000' union all
select '1A' union all
select '10A'
--------------开始查询--------------------------
select * from [tb] ORDER BY CASE WHEN PATINDEX('%[A-Z a-z]%',col)>0 THEN LEFT(col,PATINDEX('%[A-Z a-z]%',col)-1) ELSE col END ,SUBSTRING(col,PATINDEX('%[A-Z a-z]%',col),LEN(col))----------------结果----------------------------
/* col
----
1
1A
10
10A
100
1000(6 行受影响)*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-11-21 10:47:56
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(4))
insert [tb]
select '101A' union all
select '1' union all
select '1A' union all
select '2' union all
select '3' union all
select '100' union all
select '101' union all
select '10' union all
select '10A' union all
select '11'
--------------开始查询--------------------------
select * from [tb] ORDER BY CASE WHEN PATINDEX('%[A-Z a-z]%',col)>0 THEN CAST(LEFT(col,PATINDEX('%[A-Z a-z]%',col)-1) AS INT) ELSE cast(col AS int) END ,SUBSTRING(col,PATINDEX('%[A-Z a-z]%',col),LEN(col))
----------------结果----------------------------
/*
1
1A
2
3
10
10A
11
100
101
101A
*/转换成数字就OK了
declare @tab table
(
C3 nvarchar(50)
)insert into @tab(C3)
select '1'
union all
select '2'
union all
select '3'
union all
select '4'
union all
select '5'
union all
select '3A'
union all
select '1A'
union all
select '2A'
union all
select '10'
union all
select '100a'
union all
select '1000'select c3 from @tab order by convert(int,case when right(c3,1)='A' then left(c3,len(c3)-1) else c3 end ),c3
1
1A
2
2A
3
3A
4
5
10
100a
1000