有一个表,有一列是学生号,还有其他几列A、B、C学生号是由姓名和下划线加班级组成的:
比如:
甲_二班
乙_三班
丙_二班
丁_四班
A_三班
B_四班
C_二班
D_三班
现在求一条语句,要按照班级数序排列如下:
甲_二班
乙_三班
丁_四班
丙_二班
A_三班
B_四班
C_二班
D_三班
就是不要相同的班级学生在相邻的2行,这语句该怎么写?
比如:
甲_二班
乙_三班
丙_二班
丁_四班
A_三班
B_四班
C_二班
D_三班
现在求一条语句,要按照班级数序排列如下:
甲_二班
乙_三班
丁_四班
丙_二班
A_三班
B_四班
C_二班
D_三班
就是不要相同的班级学生在相邻的2行,这语句该怎么写?
insert into tb values('甲_二班')
insert into tb values('乙_三班')
insert into tb values('丙_二班')
insert into tb values('丁_四班')
insert into tb values('A_三班')
insert into tb values('B_四班')
insert into tb values('C_二班')
insert into tb values('D_三班')
goselect col from
(
select t.* , px = (select count(1) from tb where substring(col,charindex('_',col)+1,len(col)) = substring(t.col,charindex('_',t.col)+1,len(t.col))
and left(col,charindex('_',col) - 1) < left(t.col,charindex('_',t.col) - 1)
) + 1
from tb t
) m
order by px , coldrop table tb/*
col
----------
A_三班
B_四班
C_二班
D_三班
丙_二班
丁_四班
甲_二班
乙_三班(所影响的行数为 8 行)*/
select col from
(
select t.* , px = row_number() over(substring(col,charindex('_',col)+1,len(col)) ,left(col,charindex('_',col) - 1)) from tb t
) m
order by px , col
from(
select *,
px=row_number() over(partition by right(学生号,len(学生号)-charindex('_',学生号)) order by getdate())
from tb
) t
order by px,学生号
go
create table tb
(
col varchar(30)
)
insert tb values ('甲_二班')
insert tb values ('乙_三班')
insert tb values ('丙_二班')
insert tb values ('丁_四班')
insert tb values ('A_三班')
insert tb values ('B_四班')
insert tb values ('C_二班')
insert tb values ('D_三班')
goselect col from
(
select col,
(select count(*)+1 from tb where right(col,2)= right(t.col,2) and left(col,1)<left(t.col,1)) rank
from tb t
) t
order by rankcol
------------------------------
A_三班
B_四班
C_二班
D_三班
丙_二班
丁_四班
甲_二班
乙_三班(8 行受影响)
from(
select *,
px=row_number() over(partition by right(学生号,len(学生号)-charindex('_',学生号)) order by getdate())
from tb
) t
order by px,学生号
INSERT @t SELECT '甲_二班'
UNION ALL SELECT '乙_三班'
UNION ALL SELECT '丙_二班'
UNION ALL SELECT '丁_四班'
UNION ALL SELECT 'A_三班'
UNION ALL SELECT 'B_四班'
UNION ALL SELECT 'C_二班'
UNION ALL SELECT 'D_三班'
DECLARE @tt TABLE (id char(10))
INSERT @tt
SELECT * FROM @t WHERE ASCII(LEFT( id,1))>123 GROUP BY id ORDER BY UNICODE(LEFT(id,1)) DESC
INSERT @tt
SELECT * FROM @t WHERE UNICODE(LEFT(id,1)) BETWEEN 65 AND 90 GROUP BY id ORDER BY UNICODE(LEFT(id,1)) asc
id
----------
甲_二班
乙_三班
丙_二班
丁_四班
A_三班
B_四班
C_二班
D_三班
DECLARE @t TABLE (id char(10))
INSERT @t SELECT '甲_二班'
UNION ALL SELECT '乙_三班'
UNION ALL SELECT '丙_二班'
UNION ALL SELECT '丁_四班'
UNION ALL SELECT 'A_三班'
UNION ALL SELECT 'B_四班'
UNION ALL SELECT 'C_二班'
UNION ALL SELECT 'D_三班'
DECLARE @tt TABLE (id char(10))
INSERT @tt
SELECT * FROM @t WHERE ASCII(LEFT( id,1))>97 GROUP BY id ORDER BY UNICODE(LEFT(id,1)) DESC --不是字符的
INSERT @tt
SELECT * FROM @t WHERE UNICODE(LEFT(id,1)) BETWEEN 65 AND 97 GROUP BY id ORDER BY UNICODE(LEFT(id,1)) ASC -- 65到97 全是字符