Table1 字段 Str 串型
Str
2009级8班
2009级1班
2008级1班
2009级15班
2009级12班
2008级3班
2008级2班
查询语句如何些以年级班级排序
2009级1班
2009级8班
2009级12班
2009级15班
2008级1班
2008级2班
2008级3班
查询语句应该如何写?
Str
2009级8班
2009级1班
2008级1班
2009级15班
2009级12班
2008级3班
2008级2班
查询语句如何些以年级班级排序
2009级1班
2009级8班
2009级12班
2009级15班
2008级1班
2008级2班
2008级3班
查询语句应该如何写?
ORDER BY CAST(LEFT([STR],4) AS INT)DESC,[STR]
if object_id('[table1]') is not null drop table [table1]
create table [table1] (BH varchar(5))
insert into [table1]
select 'C1-01' union all
select 'C10-1' union all
select 'C10-2' union all
select 'C11-1' union all
select 'C1-02'declare @sql varchar(100)
set @sql=N'C1-01,C1-02,C10-1,C10-2,C11-1'
select * from table1
order by charindex(N','+BH+N',',N','+@sql+N',')
---------------------------------------------------------
--(1)借用字符串进行无规律排序
create table #DepartMent
(
Depart varchar(10)
)
insert into #DepartMent select '组长'
union all select '助理'
union all select '总经理'
union all select '员工'
union all select '副总经理'
union all select '主管'
declare @sql varchar(100)
set @sql=N'总经理,副总经理,主管,组长,助理,员工'
select * from #DepartMent
order by charindex(N','+Depart+N',',N','+@sql+N',')--------------------------------------------------------------create table tb00 (BH varchar(5))
insert into tb00
select 'C1-01' union all
select 'C10-1' union all
select 'C10-2' union all
select 'C11-1' union all
select 'C1-02'
goselect * from tb00 order by cast(replace(left(bh , charindex('-',bh)-1),'C','') as int) , cast(substring(bh , charindex('-',bh)+1 , len(bh)) as int) drop table tb00
-----------------------------------------------------------------
select * from table1
order by
ascii(substring(bh,1,1)),
ascii(substring(bh,2,1)),
ascii(substring(bh,3,1)),
ascii(substring(bh,4,1)),
ascii(substring(bh,5,1))
------------------------------------
INSERT @TB
SELECT N'2009级8班' UNION ALL
SELECT N'2009级1班' UNION ALL
SELECT N'2008级1班' UNION ALL
SELECT N'2009级15班' UNION ALL
SELECT N'2009级12班' UNION ALL
SELECT N'2008级3班' UNION ALL
SELECT N'2008级2班'SELECT *
FROM @TB
ORDER BY CAST(LEFT([Str],4) AS INT) DESC,
CAST(SUBSTRING([Str],CHARINDEX(N'级',[Str])+1,CHARINDEX(N'班',[Str])-CHARINDEX(N'级',[Str])-1) AS INT)
/*
Str
--------
2009级1班
2009级8班
2009级12班
2009级15班
2008级1班
2008级2班
2008级3班*/
INSERT @T SELECT N'2009级8班'
INSERT @T SELECT N'2009级1班'
INSERT @T SELECT N'2008级1班'
INSERT @T SELECT N'2009级15班'
INSERT @T SELECT N'2009级12班'
INSERT @T SELECT N'2008级3班'
INSERT @T SELECT N'2008级2班'
SELECT * FROM @T ORDER BY LEFT([STR],4)DESC,CAST(REPLACE(REPLACE([STR],N'级',''),N'班','') AS INT)ASC
/*Str
--------------------
2009级1班
2009级8班
2009级12班
2009级15班
2008级1班
2008级2班
2008级3班
*/
(
SELECT '2009级8班' AS [STR] UNION ALL
SELECT '2009级15班' UNION ALL
SELECT '2009级12班' UNION ALL
SELECT '2008级3班' UNION ALL
SELECT '2009级1班' UNION ALL
SELECT '2008级1班' UNION ALL
SELECT '2008级2班'
) B
ORDER BY charindex([STR],'2009级1班,2009级8班,2009级12班 ,2009级15班 ,2008级1班 ,2008级2班 ,2008级3班') STR
----------
2009级1班
2009级8班
2009级12班
2009级15班
2008级1班
2008级2班
2008级3班(7 行受影响)
INSERT @a SELECT '2009级8班'
union all select '2009级1班'
union all select '2008级1班'
union all select '2009级15班'
union all select '2009级12班'
union all select '2008级3班'
union all select '2008级2班' SELECT * FROM @a ORDER BY
LEFT(a,4) DESC,
CASE WHEN LEN(a)<(SELECT MAX(len(a)) FROM @a) THEN REPLACE(a,'级','级0') else a end--result
/*a
--------------------------------------------------
2009级1班
2009级8班
2009级12班
2009级15班
2008级1班
2008级2班
2008级3班(所影响的行数为 7 行)*/
(
SELECT '2009级8班' AS [STR] UNION ALL
SELECT '2009级15班' UNION ALL
SELECT '2009级12班' UNION ALL
SELECT '2008级3班' UNION ALL
SELECT '2009级1班' UNION ALL
SELECT '2008级1班' UNION ALL
SELECT '2008级2班'
) B
ORDER BY LEFT([STR],4) DESC,CAST(replace(replace([str],'级',''),'班','') AS int)STR
----------
2009级1班
2009级8班
2009级12班
2009级15班
2008级1班
2008级2班
2008级3班(7 行受影响)
DECLARE @TB TABLE([Str1] NVARCHAR(8))
INSERT @TB
SELECT N'2009级8班' UNION ALL
SELECT N'2009级1班' UNION ALL
SELECT N'2008级1班' UNION ALL
SELECT N'2009级15班' UNION ALL
SELECT N'2009级12班' UNION ALL
SELECT N'2008级3班' UNION ALL
SELECT N'2008级2班'SELECT * FROM @TB ORDER BY LEFT(STR1,4) DESC,SUBSTRING(STR1,CHARINDEX('级',STR1)+1,CHARINDEX('班',STR1)-CHARINDEX('级',STR1)-1)
SELECT * FROM
(
SELECT '2009级8班' AS [STR] UNION ALL
SELECT '2009级15班' UNION ALL
SELECT '2009级12班' UNION ALL
SELECT '2008级3班' UNION ALL
SELECT '2009级1班' UNION ALL
SELECT '2008级1班' UNION ALL
SELECT '2008级2班'
) B
ORDER BY cast(substring([STR],1,charindex(N'级'[STR])-1) as int),
cast(right(substring([STR],1,charindex(N'班',[STR])-1),2) as int)
DECLARE @Table1 TABLE ( 字段 VARCHAR(20))
INSERT INTO @Table1
SELECT '2009级8班' UNION ALL
SELECT '2009级1班' UNION ALL
SELECT '2008级1班' UNION ALL
SELECT '2009级15班' UNION ALL
SELECT '2009级12班' UNION ALL
SELECT '2008级3班' UNION ALL
SELECT '2008级2班' --Sql
SELECT * FROM @Table1 ORDER BY LEFT(字段,4) DESC , CAST(REPLACE(REPLACE(字段,'班','0'),'级','0') AS int)--Result
/*
字段
--------------------
2009级1班
2009级8班
2009级12班
2009级15班
2008级1班
2008级2班
2008级3班(7 行受影响)
*/
insert @tb
select '2009级8班' union all
select '2009级1班' union all
select '2008级1班' union all
select '2009级15班' union all
select '2009级12班' union all
select '2008级3班' union all
select '2008级2班'select * from @tb
order by cast(substring(年级,3,2) as int) desc,cast(replace(stuff(年级,1,5,''),'班','') as int)
/* (所影响的行数为 7 行)年级
--------------------
2009级1班
2009级8班
2009级12班
2009级15班
2008级1班
2008级2班
2008级3班(所影响的行数为 7 行)*/
-- Author : liangCK 小梁 & angellan 兰儿
-- Comment: 小梁 爱 兰儿
-- Date : 2009-06-08 13:44:14
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE ([Str] VARCHAR(10))
INSERT INTO @T
SELECT '2009级8班' UNION ALL
SELECT '2009级1班' UNION ALL
SELECT '2008级1班' UNION ALL
SELECT '2009级15班' UNION ALL
SELECT '2009级12班' UNION ALL
SELECT '2008级3班' UNION ALL
SELECT '2008级2班'--SQL查询如下:SELECT *
FROM @T
ORDER BY
CAST(LEFT([Str],4) AS INT) DESC,
CAST(LEFT(RIGHT([Str],CHARINDEX('级',REVERSE([Str]))-1),
PATINDEX('%[^0-9]%',RIGHT([Str],CHARINDEX('级',REVERSE([Str]))-1))-1) AS INT)
cast(replace(replace(stuff([str],1,4,''),'级',''),'班','') as int)
很好很强大
INSERT @a SELECT '2009级8班'
union all select '2009级1班'
union all select '2008级1班'
union all select '2009级15班'
union all select '2009级12班'
union all select '2008级3班'
union all select '2008级2班' SELECT * FROM @a ORDER BY
CAST (LEFT(a,4)as int) desc,
cast (left(replace(right(a,3),'级','0'),2) as int)
/*-------------
a
2009级1班
2009级8班
2009级12班
2009级15班
2008级1班
2008级2班
2008级3班
---------------*/
DECLARE @T TABLE ([Str] VARCHAR(10))
INSERT INTO @T
SELECT '2009级8班' UNION ALL
SELECT '2009级1班' UNION ALL
SELECT '2008级1班' UNION ALL
SELECT '2009级15班' UNION ALL
SELECT '2009级12班' UNION ALL
SELECT '2008级3班' UNION ALL
SELECT '2008级2班'--SQL查询如下:SELECT *
FROM @T
ORDER BY
CAST(LEFT([Str],4) AS INT) DESC,
CAST(LEFT(RIGHT([Str],CHARINDEX('级',REVERSE([Str]))-1),
PATINDEX('%[^0-9]%',RIGHT([Str],CHARINDEX('级',REVERSE([Str]))-1))-1) AS INT)这个很好,很强大,FROM 17楼
drop table orderbycreate table orderby
(strval varchar(100)
)insert into orderby values('2009级8班')
insert into orderby values('2009级1班')
insert into orderby values('2008级1班')
insert into orderby values('2009级15班')
insert into orderby values('2009级12班')
insert into orderby values('2008级3班')
insert into orderby values('2009级8班')
insert into orderby values('2008级3班')select * from orderby
order by left(strval,4) desc, convert(int,substring(strval,6,len(strval)-6))Zhge是我朋友的 也很犀利
我那个朋友的方法就可以避免啦
DECLARE @a TABLE(a NVARCHAR(200))
INSERT @a SELECT '2009级8班'
union all select '2009级1班'
union all select '2008级1班'
union all select '2009级15班'
union all select '2009级12班'
union all select '2008级3班'
union all select '2008级2班' select b.a
from (select a,convert(int,left(a,4)) as nianji,convert(int,substring(replace(a,'班',''),6,len(replace(a,'班',''))-5)) as banji from @a)b
order by nianji desc,banji
(
SELECT '2009级8班' AS [STR] UNION ALL
SELECT '2009级15班' UNION ALL
SELECT '2009级12班' UNION ALL
SELECT '2008级3班' UNION ALL
SELECT '2009级1班' UNION ALL
SELECT '2008级1班' UNION ALL
SELECT '2008级2班'
) B
drop table Table1
create table Table1
(
[Str] varchar(30)
)
insert Table1 select '2009级8班'
union all select '2009级1班'
union all select '2008级1班'
union all select '2009级15班'
union all select '2009级12班'
union all select '2008级3班'
union all select '2008级2班'select * from Table1 order by LEFT([Str],4) DESC ,
cast(replace(replace(stuff([str],1,4,''),'级',''),'班','') as int)
declare @tVal table
(
Val varchar(20)
)insert @tVal
select '2009级8班'
union all
select '2009级1班'
union all
select '2008级1班'
union all
select '2009级15班'
union all
select '2009级12班'
union all
select '2008级3班'
union all
select '2008级2班'select *
from @tVal
order by cast(left(Val,4) as int) desc,
cast(substring(reverse(Val), 2, charindex('级', reverse(Val))-2 ) as int)-- 结果集如下
Val
--------------------
2009级1班
2009级8班
2009级12班
2009级15班
2008级1班
2008级2班
2008级3班(所影响的行数为 7 行)
drop table Table1
create table Table1
(
[Str] varchar(30)
)
insert Table1 select '2009级8班'
union all select '2009级1班'
union all select '2008级1班'
union all select '2009级15班'
union all select '2009级12班'
union all select '2008级3班'
union all select '2008级2班'
select * from Table1 order by LEFT([Str],4) desc ,
convert(int,replace(right([str],len([str])-charindex('级',[str])),'班','')) asc/*
str
2009级1班
2009级8班
2009级12班
2009级15班
2008级1班
2008级2班
2008级3班 */