求SQL排考试时段

Question

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([试卷编号] int,[学号] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,5 union all
select 5,4 union all
select 6,2 union all
select 7,1 union all
select 8,4 union all
select 4,3 union all
select 5,2 union all
select 6,5
 
---查询---
select 
  试卷编号,
  学号,
  考试时段=(select count(1)+1 from tb where 学号=t.学号 and 试卷编号<t.试卷编号)
 from tb t---结果---
试卷编号        学号          考试时段        
----------- ----------- ----------- 
1           1           1
2           1           2
3           1           3
4           5           1
5           4           1
6           2           2
7           1           4
8           4           2
4           3           1
5           2           1
6           5           2（所影响的行数为 11 行）

Answer 1

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([试卷编号] int,[学号] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,5 union all
select 5,4 union all
select 6,2 union all
select 7,1 union all
select 8,4 union all
select 4,3 union all
select 5,2 union all
select 6,5
---查询---
SELECT *,
    ROW_NUMBER() OVER (PARTITION BY [学号] ORDER BY [试卷编号] desc) AS 考试时段
    FROM [tb] 
/*试卷编号        学号          考试时段
----------- ----------- --------------------
7           1           1
3           1           2
2           1           3
1           1           4
6           2           1
5           2           2
4           3           1
8           4           1
5           4           2
6           5           1
4           5           2(11 行受影响)
*/

Answer 2

declare @tb table (试卷编号 int,学号 int)
insert into @tb select 1,1 union all
                select 2,1 union all
                select 3,1 union all
                select 4,5 union all
                select 5,4 union all
                select 6,2 union all
                select 7,1 union all
                select 8,4 union all
                select 4,3 union all
                select 5,2 union all
                select 6,5
 
select *,ROW_NUMBER() over(partition by 学号 order by 试卷编号) as 考试时段 from @tb (11 行受影响)
试卷编号        学号          考试时段
----------- ----------- --------------------
1           1           1
2           1           2
3           1           3
7           1           4
5           2           1
6           2           2
4           3           1
5           4           1
8           4           2
4           5           1
6           5           2(11 行受影响)

Answer 3

-->2000
select 
  *,
  考试时段=(select count(1) from tb where 学号=t.学号 and 试卷编号<=t.试卷编号)
from tb t-->2005
SELECT
  *,
  考试时段=row_number() over(partition by 学号 order by 试卷编号 desc)
from tb

Answer 4

TO: josy（百年树人）你的测试数据改成以下，会出现同一“试卷编号”对应不同的“考试时段”：---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([试卷编号] int,[学号] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 2,5 union all
select 3,1 union all
select 4,5 union all
select 5,4 union all
select 6,2 union all
select 7,1 union all
select 8,4 union all
select 4,3 union all
select 5,2 union all
select 6,5

Answer 5

create table [tb]([试卷编号] int,[学号] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,5 union all
select 5,4 union all
select 6,2 union all
select 7,1 union all
select 8,4 union all
select 4,3 union all
select 5,2 union all
select 6,5SELECT *,ROW_NUMBER() OVER (PARTITION BY [学号] ORDER BY [试卷编号] desc) AS 考试时段 FROM [tb] 
/*试卷编号        学号          考试时段
----------- ----------- --------------------
7           1           1
3           1           2
2           1           3
1           1           4
6           2           1
5           2           2
4           3           1
8           4           1
5           4           2
6           5           1
4           5           2(11 行受影响)
*/

Answer 6

大家都没理解我的需求，row_number函数我也试过，没用。
我要的是同一个“试卷编号”对应的肯定是同一个“考试时段”，同一“考试时段”肯定不能对应有两条同一“学号”的记录

Answer 7

考虑了一会，突然明白，你问题的根本就是
怎么把你那M条课程安排到N个考场里面（N指的是一个考生最多的补考数目！）

Answer 8

这个函数是SQL2005下用的，上面有SQL2000的解决方案。

Answer 9

SQL2000的解决方案二楼的同志试过了，不行，你试试下面的测试数据就知道了---测试数据--- 
if object_id('[tb]') is not null drop table [tb] 
go 
create table [tb]([试卷编号] int,[学号] int) 
insert [tb] 
select 1,1 union all 
select 2,1 union all 
select 2,5 union all 
select 3,1 union all 
select 4,5 union all 
select 5,4 union all 
select 6,2 union all 
select 7,1 union all 
select 8,4 union all 
select 4,3 union all 
select 5,2 union all 
select 6,5 

Answer 10

--如果想减少时间.(貌似和你的需求不符)--sql 2005
create table [tb]([试卷编号] int,[学号] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,5 union all
select 5,4 union all
select 6,2 union all
select 7,1 union all
select 8,4 union all
select 4,3 union all
select 5,2 union all
select 6,5select * , 考试时段 = row_number() over(partition by 学号 order by 试卷编号 ) from tb t order by 学号 , 考试时段drop table tb/*
试卷编号        学号          考试时段
----------- ----------- --------------------
1           1           1
2           1           2
3           1           3
7           1           4
5           2           1
6           2           2
4           3           1
5           4           1
8           4           2
4           5           1
6           5           2(11 行受影响)*/create table [tb]([试卷编号] int,[学号] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,5 union all
select 5,4 union all
select 6,2 union all
select 7,1 union all
select 8,4 union all
select 4,3 union all
select 5,2 union all
select 6,5select * , 考试时段 = (select count(1) from tb where 学号 = t.学号 and 试卷编号 < t.试卷编号) + 1 from tb t order by 学号 , 考试时段drop table tb/*
试卷编号        学号          考试时段        
----------- ----------- ----------- 
1           1           1
2           1           2
3           1           3
7           1           4
5           2           1
6           2           2
4           3           1
5           4           1
8           4           2
4           5           1
6           5           2（所影响的行数为 11 行）
*/

Answer 11

很麻烦，保证学号不冲突后试卷编号又冲突了，保证试卷不冲突后学号又变得冲突了
我现在的想法是配合使用SQL的循环、判断语句，在插入新列“考试时段”后，再对有冲突的实行update，尽量减少手工判断，不知大家有什么好方法，多多赐教！

Answer 12

看这种情况，现在也不敢奢求最小考试时段，保证不冲突是首要问题。
现在想办法首先通过row_number()保证学号不冲突后，如果出现试卷编号冲突的话，就把该试卷编号对应的考试时段数字往后推，避免修改后再造成学号冲突

Answer 13

TO：dawugui and 各位csdns我现在换个思路：先确定25个考试时段（1-25），然后把试卷编号（预计1-150）随机分配到这25个时段，然后判断每个时段内是否存在有相同学号的两条记录，如果存在的话继续循环随机分配，直到每个时段不存在相同学号的两条记录，不知道这个算法可不可以实现？热烈期待你的回复~~

Answer 14

select 
  试卷编号,
  学号,
  考试时段=(select count(1)+1 from tb where 学号=t.学号 and 试卷编号<t.试卷编号)
 from tb t混分的路过