原问题:
分班原则:
1,按合理性来讲应该是按分数进行分班,因为我的表没有分数这个字段,所以就用学号来反映分数情况即学号的排序就是分数从高到低排序的结果,所以分班就以学号的顺序为依据;
2,分到学号最靠前的班,下一个学生必须是在下一批当中学号最靠后的,分到学号第2靠前的班,下一个学生必须是在下一批当中学号倒数第2靠后的。
3,班级不是固定的.
_____________原来的解答部分(完全正确的)_______
create table student(学号 nvarchar(10),姓名 varchar(12),年级 varchar(12),班级 varchar(12))
insert into student values(1101,'AA',null,null)
insert into student values(1102,'BB',null,null)
insert into student values(1103,'CC',null,null)
insert into student values(1104,'DD',null,null)
insert into student values(1105,'EE',null,null)
insert into student values(1106,'FF',null,null)
insert into student values(1107,'GG',null,null)
insert into student values(1108,'ES',null,null)
insert into student values(1109,'II',null,null)
insert into student values(1110,'AS',null,null)
insert into student values(1111,'QW',null,null)
insert into student values(1112,'PS',null,null)
insert into student values(1113,'UY',null,null) create table class(年级 varchar(12),班级 varchar(12))
insert into class values('2008级','1班')
insert into class values('2008级','2班')
insert into class values('2008级','3班')
goupdate a
set
年级=c.年级,班级=c.班级
from
student a,
(select 学号,(select count(*) from student where 学号<=stu.学号) num from student stu) b,
(select 年级,班级,(select count(*) from class where 班级<=cls.班级) num from class cls) c,
(select count(*) as num from class) as d
where
a.学号=b.学号
and
(b.num-c.num)%d.num=0
and
((b.num-c.num)/d.num)%2=0
and
a.年级 is null
update a
set
年级=c.年级,班级=c.班级
from
student a,
(select 学号,(select count(*) from student where 学号<=stu.学号) num from student stu) b,
(select 年级,班级,(select count(*) from class where 班级<=cls.班级) num from class cls) c,
(select count(*) as num from class) as d
where
a.学号=b.学号
and
(b.num+c.num)%d.num=1
and
a.年级 is nullselect * from student
/*
学号 姓名 年级 班级
----------- ------------ ------------ ------------
1101 AA 2008级 1班
1102 BB 2008级 2班
1103 CC 2008级 3班
1104 DD 2008级 3班
1105 EE 2008级 2班
1106 FF 2008级 1班
1107 GG 2008级 1班
1108 ES 2008级 2班
1109 II 2008级 3班
1110 AS 2008级 3班
1111 QW 2008级 2班
1112 PS 2008级 1班
1113 UY 2008级 1班
*/
godrop table student,class
go_________________现在要解决的问题_______________
create table student(学号 nvarchar(10),姓名 varchar(12),年级 varchar(12),班级 varchar(12))
insert into student values(1101,'AA',null,null)
insert into student values(1102,'BB',null,null)
insert into student values(1103,'CC','未分班',null)
insert into student values(1104,'DD','未分班',null)
insert into student values(1105,'EE','未分班',null)
insert into student values(1106,'FF','未分班',null)
insert into student values(1107,'GG','未分班',null)
insert into student values(1108,'ES','未分班',null)
insert into student values(1109,'II','未分班',null)
insert into student values(1110,'AS','未分班',null)
insert into student values(1111,'QW','未分班',null)
insert into student values(1112,'PS','未分班',null)
insert into student values(1113,'UY','未分班',null) create table class(年级 varchar(12),班级 varchar(12))
insert into class values('2008级','1班')
insert into class values('2008级','2班')
insert into class values('2008级','3班')
go
现在只对student表中的班级='未分班'的学生进行分班(注意:不是对student表的所有学生进行分班).如果还是用原来的SQL就有问题,请问这样修改上面的SQL?
分班原则:
1,按合理性来讲应该是按分数进行分班,因为我的表没有分数这个字段,所以就用学号来反映分数情况即学号的排序就是分数从高到低排序的结果,所以分班就以学号的顺序为依据;
2,分到学号最靠前的班,下一个学生必须是在下一批当中学号最靠后的,分到学号第2靠前的班,下一个学生必须是在下一批当中学号倒数第2靠后的。
3,班级不是固定的.
_____________原来的解答部分(完全正确的)_______
create table student(学号 nvarchar(10),姓名 varchar(12),年级 varchar(12),班级 varchar(12))
insert into student values(1101,'AA',null,null)
insert into student values(1102,'BB',null,null)
insert into student values(1103,'CC',null,null)
insert into student values(1104,'DD',null,null)
insert into student values(1105,'EE',null,null)
insert into student values(1106,'FF',null,null)
insert into student values(1107,'GG',null,null)
insert into student values(1108,'ES',null,null)
insert into student values(1109,'II',null,null)
insert into student values(1110,'AS',null,null)
insert into student values(1111,'QW',null,null)
insert into student values(1112,'PS',null,null)
insert into student values(1113,'UY',null,null) create table class(年级 varchar(12),班级 varchar(12))
insert into class values('2008级','1班')
insert into class values('2008级','2班')
insert into class values('2008级','3班')
goupdate a
set
年级=c.年级,班级=c.班级
from
student a,
(select 学号,(select count(*) from student where 学号<=stu.学号) num from student stu) b,
(select 年级,班级,(select count(*) from class where 班级<=cls.班级) num from class cls) c,
(select count(*) as num from class) as d
where
a.学号=b.学号
and
(b.num-c.num)%d.num=0
and
((b.num-c.num)/d.num)%2=0
and
a.年级 is null
update a
set
年级=c.年级,班级=c.班级
from
student a,
(select 学号,(select count(*) from student where 学号<=stu.学号) num from student stu) b,
(select 年级,班级,(select count(*) from class where 班级<=cls.班级) num from class cls) c,
(select count(*) as num from class) as d
where
a.学号=b.学号
and
(b.num+c.num)%d.num=1
and
a.年级 is nullselect * from student
/*
学号 姓名 年级 班级
----------- ------------ ------------ ------------
1101 AA 2008级 1班
1102 BB 2008级 2班
1103 CC 2008级 3班
1104 DD 2008级 3班
1105 EE 2008级 2班
1106 FF 2008级 1班
1107 GG 2008级 1班
1108 ES 2008级 2班
1109 II 2008级 3班
1110 AS 2008级 3班
1111 QW 2008级 2班
1112 PS 2008级 1班
1113 UY 2008级 1班
*/
godrop table student,class
go_________________现在要解决的问题_______________
create table student(学号 nvarchar(10),姓名 varchar(12),年级 varchar(12),班级 varchar(12))
insert into student values(1101,'AA',null,null)
insert into student values(1102,'BB',null,null)
insert into student values(1103,'CC','未分班',null)
insert into student values(1104,'DD','未分班',null)
insert into student values(1105,'EE','未分班',null)
insert into student values(1106,'FF','未分班',null)
insert into student values(1107,'GG','未分班',null)
insert into student values(1108,'ES','未分班',null)
insert into student values(1109,'II','未分班',null)
insert into student values(1110,'AS','未分班',null)
insert into student values(1111,'QW','未分班',null)
insert into student values(1112,'PS','未分班',null)
insert into student values(1113,'UY','未分班',null) create table class(年级 varchar(12),班级 varchar(12))
insert into class values('2008级','1班')
insert into class values('2008级','2班')
insert into class values('2008级','3班')
go
现在只对student表中的班级='未分班'的学生进行分班(注意:不是对student表的所有学生进行分班).如果还是用原来的SQL就有问题,请问这样修改上面的SQL?
insert into student values(1101,'AA',null,null)
insert into student values(1102,'BB',null,null)
insert into student values(1103,'CC','未分班',null)
insert into student values(1104,'DD','未分班',null)
insert into student values(1105,'EE','未分班',null)
insert into student values(1106,'FF','未分班',null)
insert into student values(1107,'GG','未分班',null)
insert into student values(1108,'ES','未分班',null)
insert into student values(1109,'II','未分班',null)
insert into student values(1110,'AS','未分班',null)
insert into student values(1111,'QW','未分班',null)
insert into student values(1112,'PS','未分班',null)
insert into student values(1113,'UY','未分班',null) create table class(年级 varchar(12),班级 varchar(12))
insert into class values('2008级','1班')
insert into class values('2008级','2班')
insert into class values('2008级','3班')
update f set 年级=g.nj,班级 = g.bj
from student f,(select xh,nj,bj from
(select a.学号 as xh,(select count(*) from student where 学号<a.学号 and 年级 = '未分班') as xhxh from student a where 年级='未分班') c,
(select b.年级 as nj,b.班级 as bj,(select count(*) from class where 年级+班级<b.年级+b.班级) as bjxh from class b ) d,
(select count(*) as zs from class) e
where (xhxh%(2*zs)= bjxh or xhxh%(2*zs)-2*(xhxh%zs) -1 = bjxh)) g
where f.学号=xh and f.年级= '未分班'
select * from student
/*学号 姓名 年级 班级
---------- ------------ ------------ ------------
1101 AA NULL NULL
1102 BB NULL NULL
1103 CC 2008级 1班
1104 DD 2008级 2班
1105 EE 2008级 3班
1106 FF 2008级 3班
1107 GG 2008级 2班
1108 ES 2008级 1班
1109 II 2008级 1班
1110 AS 2008级 2班
1111 QW 2008级 3班
1112 PS 2008级 3班
1113 UY 2008级 2班
*/
drop table student,class