---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([课程名称] varchar(5),[课程类别] int) insert [tb] select '课程1',0 union all select '课程2',0 union all select '课程3',0 union all select '课程4',1 union all select '课程5',1 union all select '课程6',1 union all select '课程7',0 union all select '课程8',1
---查询--- select 课程名称, 课程类别 from (select *,px=row_number() over(partition by 课程类别 order by 课程名称) from tb) t order by px, 课程类别---结果--- 课程名称 课程类别 ----- ----------- 课程1 0 课程4 1 课程2 0 课程5 1 课程3 0 课程6 1 课程7 0 课程8 1(8 行受影响)
select t.* , px = (select count(1) from tb where 课程类别 = t.课程类别 and 课程名称 < t.课程名称) + 1 from tb t order by px , 课程类别
--sql 2005用一楼的row_number,2000用如下的子查询 create table [tb]([课程名称] varchar(5),[课程类别] int) insert [tb] select '课程1',0 union all select '课程2',0 union all select '课程3',0 union all select '课程4',1 union all select '课程5',1 union all select '课程6',1 union all select '课程7',0 union all select '课程8',1select t.* , px = (select count(1) from tb where 课程类别 = t.课程类别 and 课程名称 < t.课程名称) + 1 from tb t order by px , 课程类别drop table tb/* 课程名称 课程类别 px ----- ----------- ----------- 课程1 0 1 课程4 1 1 课程2 0 2 课程5 1 2 课程3 0 3 课程6 1 3 课程7 0 4 课程8 1 4(所影响的行数为 8 行) */
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([课程名称] varchar(5),[课程类别] int)
insert [tb]
select '课程1',0 union all
select '课程2',0 union all
select '课程3',0 union all
select '课程4',1 union all
select '课程5',1 union all
select '课程6',1 union all
select '课程7',0 union all
select '课程8',1
---查询---
select
课程名称,
课程类别
from
(select *,px=row_number() over(partition by 课程类别 order by 课程名称) from tb) t
order by
px,
课程类别---结果---
课程名称 课程类别
----- -----------
课程1 0
课程4 1
课程2 0
课程5 1
课程3 0
课程6 1
课程7 0
课程8 1(8 行受影响)
order by px , 课程类别
create table [tb]([课程名称] varchar(5),[课程类别] int)
insert [tb]
select '课程1',0 union all
select '课程2',0 union all
select '课程3',0 union all
select '课程4',1 union all
select '课程5',1 union all
select '课程6',1 union all
select '课程7',0 union all
select '课程8',1select t.* , px = (select count(1) from tb where 课程类别 = t.课程类别 and 课程名称 < t.课程名称) + 1 from tb t
order by px , 课程类别drop table tb/*
课程名称 课程类别 px
----- ----------- -----------
课程1 0 1
课程4 1 1
课程2 0 2
课程5 1 2
课程3 0 3
课程6 1 3
课程7 0 4
课程8 1 4(所影响的行数为 8 行)
*/