新生分班第5问,JiangHongTao已经很好的解答,但是还有一个问题很麻烦...

[yezby20080306]

目的:新生分班
有一表TS:
编号 姓名 性别 年级 班级 总分
1 AA 女 未分班 620
2 BB 男 未分班 310
3 CC 男 未分班 420
4 DD 女 未分班 325
5 EE 女 未分班 190
6 EW 女 未分班 590
7 EW 女 未分班 593
...
有一表TC:
编号 年级 班级
1 初一 1班
1 初一 2班
1 初一 3班
...
条件:
1,表TC的用户添加的班级(动态);
2,分班原则:按总分字段分班即:1班分得一个最高分,3班分得次高分,2班分得最低分;在下一批中:2班分得剩下的最高分,3班分得剩下的次高分,1班分得剩下的最低分(即首尾相连);
3,我是需要对TS表的记录进行UPDATE操作,不是生成一个新表!!!
____________________________
jianghongtao的解答:
create table student(总分 decimal(9,2),姓名 varchar(12),年级 varchar(12),班级 varchar(12))
insert into student values(1101,'AA','未分班',null)
insert into student values(1102,'BB','未分班',null)
insert into student values(1103,'CC','未分班',null)
insert into student values(110,'DD','未分班',null)
insert into student values(110,'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 order by 总分
drop table student,class
_________________________________
总分没有重复时是没有问题的,当总分有重复时,分班就有问题(总分相同的班级是相同的),怎么解决这个问题?

[yezby20080306]

TO DAWUGUI:
除非你的表改为这样:
有一表TC:
编号 年级 班级
1 初一 1班
3 初一 2班
2 初一 3班

然后通过px%(select count(*) from tc)的取值和编号对应才行.
______________________
TC表就是这样的啊:
表TC:
编号 年级 班级
1 初一 1班
3 初一 2班
2 初一 3班
TC是动态的,总分有相同时也能正常分班,你给我一个完整的SQL好吗 ?谢过!

[dawugui]

create table ts(编号 int , 姓名 varchar(10) , 性别 varchar(10) , 年级 varchar(10) , 班级 varchar(10) , 总分 int) 
insert into ts values(1, 'AA' , '女' , '' , '' , 620 )
insert into ts values(2, 'BB' , '男' , '' , '' , 310 )
insert into ts values(3, 'CC' , '男' , '' , '' , 420 )
insert into ts values(4, 'DD' , '女' , '' , '' , 325 )
insert into ts values(5, 'EE' , '女' , '' , '' , 190 )
insert into ts values(6, 'EW' , '女' , '' , '' , 590 )
insert into ts values(7, 'EW' , '女' , '' , '' , 593 )
create table tc(编号 int,年级 varchar(10) , 班级 varchar(10))
insert into tc values(1,   '初一', '1班')              
insert into tc values(3,   '初一', '2班')               
insert into tc values(2,   '初一', '3班')   
go

--查询
select m.编号,m.姓名,m.性别,n.年级,n.班级,m.总分 from
(
  select t.* , px = (((select count(1) from ts where 总分 > t.总分 or (总分 = t.总分 and 编号 > t.编号)) + 1)-1)% (select count(*) cnt from tc) from ts t
) m,tc n
where m.px = n.编号 - 1
/*
编号          姓名         性别         年级         班级         总分          
----------- ---------- ---------- ---------- ---------- ----------- 
1           AA         女          初一         1班         620
2           BB         男          初一         2班         310
3           CC         男          初一         1班         420
4           DD         女          初一         3班         325
5           EE         女          初一         1班         190
6           EW         女          初一         2班         590
7           EW         女          初一         3班         593

（所影响的行数为 7 行）
*/

--更新
update ts
set 年级 = o.年级,班级 = o.班级
from ts,(select m.编号,m.姓名,m.性别,n.年级,n.班级,m.总分 from
(
  select t.* , px = (((select count(1) from ts where 总分 > t.总分 or (总分 = t.总分 and 编号 > t.编号)) + 1)-1)% (select count(*) cnt from tc) from ts t
) m,tc n
where m.px = n.编号 - 1
) o
where ts.编号 = o.编号

select * from ts
/*
编号          姓名         性别         年级         班级         总分          
----------- ---------- ---------- ---------- ---------- ----------- 
1           AA         女          初一         1班         620
2           BB         男          初一         2班         310
3           CC         男          初一         1班         420
4           DD         女          初一         3班         325
5           EE         女          初一         1班         190
6           EW         女          初一         2班         590
7           EW         女          初一         3班         593

（所影响的行数为 7 行）
*/

--drop table ts,tc

[dawugui]

--来个同分，你看看结果对不对？
create table ts(编号 int , 姓名 varchar(10) , 性别 varchar(10) , 年级 varchar(10) , 班级 varchar(10) , 总分 int) 
insert into ts values(1, 'AA' , '女' , '' , '' , 620 )
insert into ts values(2, 'BB' , '男' , '' , '' , 310 )
insert into ts values(3, 'CC' , '男' , '' , '' , 420 )
insert into ts values(4, 'DD' , '女' , '' , '' , 325 )
insert into ts values(5, 'EE' , '女' , '' , '' , 190 )
insert into ts values(6, 'EW' , '女' , '' , '' , 590 )
insert into ts values(7, 'EW' , '女' , '' , '' , 593 )
insert into ts values(8, 'FW' , '女' , '' , '' , 593 )
create table tc(编号 int,年级 varchar(10) , 班级 varchar(10))
insert into tc values(1,   '初一', '1班')              
insert into tc values(3,   '初一', '2班')               
insert into tc values(2,   '初一', '3班')   
go

--查询
select m.编号,m.姓名,m.性别,n.年级,n.班级,m.总分 from
(
  select t.* , px = (((select count(1) from ts where 总分 > t.总分 or (总分 = t.总分 and 编号 > t.编号)) + 1)-1)% (select count(*) cnt from tc) from ts t
) m,tc n
where m.px = n.编号 - 1

--更新
update ts
set 年级 = o.年级,班级 = o.班级
from ts,(select m.编号,m.姓名,m.性别,n.年级,n.班级,m.总分 from
(
  select t.* , px = (((select count(1) from ts where 总分 > t.总分 or (总分 = t.总分 and 编号 > t.编号)) + 1)-1)% (select count(*) cnt from tc) from ts t
) m,tc n
where m.px = n.编号 - 1
) o
where ts.编号 = o.编号

select * from ts

drop table ts,tc

/*编号          姓名         性别         年级         班级         总分          
----------- ---------- ---------- ---------- ---------- ----------- 
1           AA         女          初一         1班         620
2           BB         男          初一         1班         310
3           CC         男          初一         3班         420
4           DD         女          初一         2班         325
5           EE         女          初一         3班         190
6           EW         女          初一         1班         590
7           EW         女          初一         2班         593
8           FW         女          初一         3班         593

（所影响的行数为 8 行）
*/

[yezby20080306]

谢谢,测试了,还有问题,这个问题看来很难的,那么有什么办法把一个表中的一个字段如总分相同的依次加0.01,是总分都不相同?呵呵,就是保证总分没有相同的!

[yezby20080306]

结了再开一帖,看看

[pt1314917]

很好，顶``

[dawugui]

看看.

[dawugui]

1班分得一个最高分,3班分得次高分,2班分得最低分;在下一批中:2班分得剩下的最高分,3班分得剩下的次高分,1班分得剩下的最低分(即首尾相连);

--

一个排名不就解决了?

[dawugui]

总分相同的班级是相同的

如何分班?

[dawugui]

总分相同的班级是相同的

相同的时候再按姓名(或编号)排序,可否?

[dawugui]

--search
select 编号,姓名,性别,年级,总分,班级 = case px%3 when 1 then '1班' when 2 then '3班' else '2班' end from
( 
  select t.* , px = (select count(1) from student where 总分 > t.总分 or (总分 = t.总分 and 编号 > t.编号)) + 1 from student t
) m

[dawugui]

--search
select 编号,姓名,性别,年级,总分,班级 = case px%3 when 1 then '1班' when 2 then '3班' else '2班' end from
( 
  select t.* , px = (select count(1) from student where 总分 > t.总分 or (总分 = t.总分 and 编号 > t.编号)) + 1 from student t
) m

--update
update student
set 班级 = n.班级
from student , (select 编号,姓名,性别,年级,总分,班级 = case px%3 when 1 then '1班' when 2 then '3班' else '2班' end from
( 
  select t.* , px = (select count(1) from student where 总分 > t.总分 or (总分 = t.总分 and 编号 > t.编号)) + 1 from student t
) m
) n
where student.编号 = n.编号

[dawugui]

create table student(编号 int , 姓名 varchar(10) , 性别 varchar(10) , 年级 varchar(10) , 班级 varchar(10) , 总分 int) 
insert into student values(1, 'AA' , '女' , '' , '' , 620 )
insert into student values(2, 'BB' , '男' , '' , '' , 310 )
insert into student values(3, 'CC' , '男' , '' , '' , 420 )
insert into student values(4, 'DD' , '女' , '' , '' , 325 )
insert into student values(5, 'EE' , '女' , '' , '' , 190 )
insert into student values(6, 'EW' , '女' , '' , '' , 590 )
insert into student values(7, 'EW' , '女' , '' , '' , 593 )
go

--search
select 编号,姓名,性别,年级,总分,班级 = case px%3 when 1 then '1班' when 2 then '3班' else '2班' end from
( 
  select t.* , px = (select count(1) from student where 总分 > t.总分 or (总分 = t.总分 and 编号 > t.编号)) + 1 from student t
) m
/*
编号          姓名         性别         年级         总分          班级   
----------- ---------- ---------- ---------- ----------- ---- 
1           AA         女                     620         1班
2           BB         男                     310         2班
3           CC         男                     420         1班
4           DD         女                     325         3班
5           EE         女                     190         1班
6           EW         女                     590         2班
7           EW         女                     593         3班
（所影响的行数为 7 行）
*/

--update
update student
set 班级 = n.班级
from student , (select 编号,姓名,性别,年级,总分,班级 = case px%3 when 1 then '1班' when 2 then '3班' else '2班' end from
( 
  select t.* , px = (select count(1) from student where 总分 > t.总分 or (总分 = t.总分 and 编号 > t.编号)) + 1 from student t
) m
) n
where student.编号 = n.编号
select * from student
/*
编号          姓名         性别         年级         班级         总分          
----------- ---------- ---------- ---------- ---------- ----------- 
1           AA         女                     1班         620
2           BB         男                     2班         310
3           CC         男                     1班         420
4           DD         女                     3班         325
5           EE         女                     1班         190
6           EW         女                     2班         590
7           EW         女                     3班         593

（所影响的行数为 7 行）
*/
drop table student

[yezby20080306]

TO DAWUGUI:
谢谢!
有个问题:班级表TC的用户添加的班级是动态的;

[yezby20080306]

当总分相同的时候再按编号排序可能是一个好的办法.我想怎样把这个想法加到jianghongtao的SQL语句中?

[dawugui]

[Quote=引用 10 楼 yezby20080306 的回复:]
当总分相同的时候再按编号排序可能是一个好的办法.我想怎样把这个想法加到jianghongtao的SQL语句中?
[/Quote]

我在8楼不是已经写了语句了?

[dawugui]

[Quote=引用 9 楼 yezby20080306 的回复:]
TO DAWUGUI:
谢谢!
有个问题:班级表TC的用户添加的班级是动态的;
[/Quote]

这个不行吧.

除非你的表改为这样:
有一表TC:
编号 年级 班级
1 初一 1班
3 初一 2班
2 初一 3班

然后通过px%(select count(*) from tc)的取值和编号对应才行.