有2表:
create table tb1(学号 int,姓名 varchar(10),年级 varchar(10),班级 varchar(10))
insert into tb1 values('111','AA',null,null)
insert into tb1 values('112','BB',null,null)
insert into tb1 values('113','CC',null,null)
insert into tb1 values('113','sc',null,null)
insert into tb1 values('120','DD',null,null)
insert into tb1 values('121','EE',null,null)
insert into tb1 values('122','FF',null,null)
insert into tb1 values('123','GG',null,null)
insert into tb1 values('124','HH',null,null)
insert into tb1 values('125','II',null,null)
insert into tb1 values('126','JJ',null,null)
create table tb2(年级 varchar(10),班级 varchar(10))
insert into tb2 values('2010级', 1班)
insert into tb2 values('2010级', 2班)
insert into tb2 values('2010级', 3班)
insert into tb2 values('2010级', 4班)
go
分班原则:
1,按合理性来讲应该是按分数进行分班,因为我的表没有分数这个字段,所以就用学号来反映分数情况即学号的排序就是分数从高到低排序的结果,所以分班就以学号的顺序为依据;
2,分到学号最靠前的班,下一个学生必须是在下一批当中学号最靠后的;分到学号第2靠前的班,下一个学生必须是在下一批当中学号倒数第2靠后的;如上表,班级分得的学号第一批:111\112\113\114 那第2批就应该是:123\122\121\120
create table tb1(学号 int,姓名 varchar(10),年级 varchar(10),班级 varchar(10))
insert into tb1 values('111','AA',null,null)
insert into tb1 values('112','BB',null,null)
insert into tb1 values('113','CC',null,null)
insert into tb1 values('113','sc',null,null)
insert into tb1 values('120','DD',null,null)
insert into tb1 values('121','EE',null,null)
insert into tb1 values('122','FF',null,null)
insert into tb1 values('123','GG',null,null)
insert into tb1 values('124','HH',null,null)
insert into tb1 values('125','II',null,null)
insert into tb1 values('126','JJ',null,null)
create table tb2(年级 varchar(10),班级 varchar(10))
insert into tb2 values('2010级', 1班)
insert into tb2 values('2010级', 2班)
insert into tb2 values('2010级', 3班)
insert into tb2 values('2010级', 4班)
go
分班原则:
1,按合理性来讲应该是按分数进行分班,因为我的表没有分数这个字段,所以就用学号来反映分数情况即学号的排序就是分数从高到低排序的结果,所以分班就以学号的顺序为依据;
2,分到学号最靠前的班,下一个学生必须是在下一批当中学号最靠后的;分到学号第2靠前的班,下一个学生必须是在下一批当中学号倒数第2靠后的;如上表,班级分得的学号第一批:111\112\113\114 那第2批就应该是:123\122\121\120
第二批8/7/6/5
第三批9/10/11/12
第四批16/15/14/13
_______________________________
这个给我做的话我是这样做,写个循环,mod/818 27 36 45分别插入四个班
不过以8个人为单位分批会更好一点吧,那样的话就可以循环实现了,
每个班两人,18,27,36,45
create table tb1(num int,name varchar(10),grade varchar(10),class int)
insert into tb1 values('111','AA',null,null)
insert into tb1 values('112','BB',null,null)
insert into tb1 values('113','CC',null,null)
insert into tb1 values('120','DD',null,null)
insert into tb1 values('121','EE',null,null)
insert into tb1 values('122','FF',null,null)
insert into tb1 values('123','GG',null,null)
insert into tb1 values('124','HH',null,null)
insert into tb1 values('125','II',null,null)
insert into tb1 values('126','JJ',null,null)
insert into tb1 values('127','AA',null,null)
insert into tb1 values('128','BB',null,null)
insert into tb1 values('129','CC',null,null)
insert into tb1 values('130','DD',null,null)
insert into tb1 values('131','EE',null,null)
insert into tb1 values('132','FF',null,null)
insert into tb1 values('133','GG',null,null)
insert into tb1 values('134','HH',null,null)
insert into tb1 values('135','II',null,null)
insert into tb1 values('136','JJ',null,null)
GO--寫個班級固定的,假設有4個班
select
case when id%2=0 then a1 else a4 end as a1,
case when id%2=0 then a2 else a3 end as a2,
case when id%2=0 then a3 else a2 end as a3,
case when id%2=0 then a4 else a1 end as a4
from
(
select (id-1)/4 as id,
max(case when id%4=1 then num end) as a1,
max(case when id%4=2 then num end) as a2,
max(case when id%4=3 then num end) as a3,
max(case when id%4=0 then num end) as a4
from (select id=(select count(*) from tb1 where num<=a.num),* from tb1 a) b
group by (id-1)/4
) T/*a1 a2 a3 a4
----------- ----------- ----------- -----------
111 112 113 120
124 123 122 121
125 126 127 128
132 131 130 129
133 134 135 136
*/drop table tb1
declare @j intdeclare @sql varchar(5000)
set @sql = 'select '
set @i = 1
set @j = 4
while @i <= @j
begin
set @sql = @sql + ' ''a'+convert(varchar(2),@i)+''','
set @i=@i+1
end
set @sql =left (@sql,len(@sql)-1) exec (@sql)
select
case when id%2=0 then a1 else a4 end as a1,
case when id%2=0 then a2 else a3 end as a2,
case when id%2=0 then a3 else a2 end as a3,
case when id%2=0 then a4 else a1 end as a4
from
(
select (id-1)/4 as id,
max(case when id%4=1 then num end) as a1,
max(case when id%4=2 then num end) as a2,
max(case when id%4=3 then num end) as a3,
max(case when id%4=0 then num end) as a4
from (select id=(select count(*) from tb1 where num<=a.num),* from tb1 a) b
group by (id-1)/4
) T你把带有case 和 max的地方用循环生成就可以了