--建立測試環境
Create Table Tb(Aid int,姓名 Nvarchar(20),才能 Nvarchar(20))
Insert Tb Select 1,N'小红',N'画画'
Union all Select 2,N'小明',N'英语'
Union all Select 3,N'小刚',N'唱歌'
Union all Select 4,N'小红',N'朗诵'
Union all Select 5,N'小明',N'诗歌'
--Select * from Tb
--測試
Select
(Select Count(*)+1 From Tb Where 姓名<A.姓名 Or (姓名=A.姓名 And Aid<A.Aid )) As id,
姓名,
才能
From Tb A
Order By 姓名,id
--刪除測試環境
Drop Table Tb
--結果
/*
id 姓名 才能
1 小明 英语
2 小明 诗歌
3 小刚 唱歌
4 小红 画画
5 小红 朗诵
*/
create table #t3(Aid int,name varchar(10),Capacity varchar(10))
insert into #t3
select 1,'小红','画画' union all
select 2,'小明','英语' union all
select 3,'小刚','唱歌' union all
select 4,'小红','朗诵' union all
select 5,'小明','诗歌'
go
select id=identity(int),a.* into tabb from (select top 100 PERCENT name,capacity from #t3
order by charindex(','+name+',',',小红,小明,小刚,'))a
go
select * from tabb
create table #t3(Aid int,name varchar(10),Capacity varchar(10))
insert into #t3
select 1,'小红','画画' union all
select 2,'小明','英语' union all
select 3,'小刚','唱歌' union all
select 4,'小红','朗诵' union all
select 5,'小明','诗歌'
select name,capacity into tab from #t3 order by charindex(','+name+',',',小红,小明,小刚,')
select * from tab
use pubs
go
--建立测试数据---------------------------------------
Create Table Tb(Aid int,姓名 varchar(20),才能 varchar(20))
insert Tb select 1,'小红','画画'
union all select 2,'小明','英语'
union all select 3,'小刚','唱歌'
union all select 4,'小红', '朗诵'
union all select 5,'小明','诗歌'
select * from Tb
GO
--将排序好的整张表的结果放置到临时表
select IDENTITY(int,1,1) as Aid,姓名,才能
into #Tmp
from Tb
order by 姓名 desc
--清除Tb中的数据,然后从临时表中重导入
TRUNCATE TABLE Tb
insert into Tb select * from #Tmp
create table #t3(Aid int,name varchar(10),Capacity varchar(10))
insert into #t3
select 1,'小红','画画' union all
select 2,'小明','英语' union all
select 3,'小刚','唱歌' union all
select 4,'小红','朗诵' union all
select 5,'小明','诗歌'
----------
select *,identity(int,1,1) as Aid into t1 from
(
select top 100 name,capacity from #t3
order by case name when '小红' then 1
when '小明' then 2 else 3 end) A
--Try It