• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

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

yezby20080306 2008-03-10 10:18:45
目的:新生分班
有一表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
_________________________________
总分没有重复时是没有问题的,当总分有重复时,分班就有问题(总分相同的班级是相同的),怎么解决这个问题?
...全文
86 点赞 收藏 17
写回复
17 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dawugui 2008-03-10
[Quote=引用 9 楼 yezby20080306 的回复:]
TO DAWUGUI:
谢谢!
有个问题:班级表TC的用户添加的班级是动态的;
[/Quote]

这个不行吧.

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

然后通过px%(select count(*) from tc)的取值和编号对应才行.
回复
dawugui 2008-03-10
[Quote=引用 10 楼 yezby20080306 的回复:]
当总分相同的时候再按编号排序可能是一个好的办法.我想怎样把这个想法加到jianghongtao的SQL语句中?
[/Quote]

我在8楼不是已经写了语句了?
回复
yezby20080306 2008-03-10
当总分相同的时候再按编号排序可能是一个好的办法.我想怎样把这个想法加到jianghongtao的SQL语句中?
回复
yezby20080306 2008-03-10
TO DAWUGUI:
谢谢!
有个问题:班级表TC的用户添加的班级是动态的;
回复
dawugui 2008-03-10
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
回复
dawugui 2008-03-10
--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 2008-03-10

--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 2008-03-10
总分相同的班级是相同的

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

回复
dawugui 2008-03-10
总分相同的班级是相同的

如何分班?
回复
dawugui 2008-03-10
1班分得一个最高分,3班分得次高分,2班分得最低分;在下一批中:2班分得剩下的最高分,3班分得剩下的次高分,1班分得剩下的最低分(即首尾相连);

--

一个排名不就解决了?
回复
dawugui 2008-03-10
看看.
回复
pt1314917 2008-03-10
很好,顶``
回复
yezby20080306 2008-03-10
结了再开一帖,看看
回复
yezby20080306 2008-03-10
谢谢,测试了,还有问题,这个问题看来很难的,那么有什么办法把一个表中的一个字段如总分相同的依次加0.01,是总分都不相同?呵呵,就是保证总分没有相同的!
回复
dawugui 2008-03-10
--来个同分,你看看结果对不对?
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 行)
*/
回复
dawugui 2008-03-10
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
回复
yezby20080306 2008-03-10
TO DAWUGUI:
除非你的表改为这样:
有一表TC:
编号 年级 班级
1 初一 1班
3 初一 2班
2 初一 3班

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

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-10 10:18
社区公告
暂无公告