求一SQL语句,关于新生分班

Question

描述:
新生数据表:
学号 姓名 年级 班级
111  AA  
112  BB
113  CC
120  DD
121  EE
...  ..
班级数据表:
年级   班级
2010级 1
2010级 2
2010级 3
2010级 4
条件:
新生人数已知;年级\班级已知;
要求:按学号分班,

Answer 1

或者你给我一个新生分班的SQL语句也可以啊.谢谢了!

Answer 2

create table tb1(学号 int,姓名 varchar(10),年级 varchar(10),班级 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) 
create table tb2(年级 varchar(10),班级 int) 
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
declare @cnt as int
select @cnt = count(*) from tb2select t1.学号,t1.姓名,t2.年级,t2.班级 from
(
  select *,((px-1)%@cnt) + 1 banji from
  (
    SELECT *,px=(SELECT COUNT(DISTINCT 学号) FROM tb1 WHERE 学号 < a.学号) + 1 FROM tb1 a
  ) t
) t1,
(
  SELECT *,px=(SELECT COUNT(1) FROM tb2 WHERE 年级 < a.年级 or (年级 = a.年级 and 班级 < a.班级)) + 1 FROM tb2 a
) t2
where t1.banji = t2.px
order by t1.学号drop table tb1,tb2/*
学号          姓名         年级         班级          
----------- ---------- ---------- ----------- 
111         AA         2010级      1
112         BB         2010级      2
113         CC         2010级      3
120         DD         2010级      4
121         EE         2010级      1
122         FF         2010级      2
123         GG         2010级      3
124         HH         2010级      4
125         II         2010级      1
126         JJ         2010级      2（所影响的行数为 10 行）
*/

Answer 3

学号不是数字型 你可以convert()或者cast()后就是数字型了嘛

Answer 4

你这样好象是显示的效果啊,并没有update语句那tb1应该没有更新吧,我需要的是更新tb1的年级\班级,在麻烦你给我看看吧,非常感谢!!!

Answer 5

实际上我还是没有搞懂(初学SQL,对不起),我的要求:
1\学号的顺序实际上就是分数的排序,不能随机选;
2\要用update语句更新tb1中的年级和班级;
比如:(说明:第1名,就是第一个学号;第2名,就是第二个学号...)
假设:第1名->1班 ;第2名->3班;第3名->2班;第4名->4班
必定:第5名->1班 ;第6名->3班;第7名->2班;第8名->4班

Answer 6

--把学号<a.学号改为分数<a.分数就行了．
declare @cnt as int
select @cnt = count(*) from tb2select t1.学号,t1.姓名,t2.年级,t2.班级 from
(
  select *,((px-1)%@cnt) + 1 banji from
  (
    SELECT *,px=(SELECT COUNT(DISTINCT 学号) FROM tb1 WHERE 分数 < a.分数) + 1 FROM tb1 a
  ) t
) t1,
(
  SELECT *,px=(SELECT COUNT(1) FROM tb2 WHERE 年级 < a.年级 or (年级 = a.年级 and 班级 < a.班级)) + 1 FROM tb2 a
) t2
where t1.banji = t2.px
order by t1.学号

Answer 7

至于更新，把上面的查询做为子查询就行了．update t1
set 年级 = t.年级,班级 = t.班级
from t1,(select t1.学号,t1.姓名,t2.年级,t2.班级 from
(
  select *,((px-1)%@cnt) + 1 banji from
  (
    SELECT *,px=(SELECT COUNT(DISTINCT 学号) FROM tb1 WHERE 学号 < a.学号) + 1 FROM tb1 a
  ) t
) t1,
(
  SELECT *,px=(SELECT COUNT(1) FROM tb2 WHERE 年级 < a.年级 or (年级 = a.年级 and 班级 < a.班级)) + 1 FROM tb2 a
) t2
where t1.banji = t2.px
order by t1.学号
) t
where t1.学号 = t.学号

Answer 8

写个2000随机分配的：--原始数据：@New
declare @New table (学号 int,姓名 varchar(2),年级 varchar(6),班级 int)
insert @New
select 111,'AA',null,null union all
select 112,'BB',null,null union all
select 113,'CC',null,null union all
select 120,'DD',null,null union all
select 121,'EE',null,null
--原始数据：@Grade
declare @Grade table (年级 varchar(6),班级 int)
insert @Grade
select '2010级',1 union all
select '2010级',2 union all
select '2010级',3 union all
select '2010级',4--新生随机
create table #New (SID int identity,学号 int,姓名 varchar(2))
insert #New(学号,姓名) select 学号,姓名 from @New order by newid()declare @Max int,@年级 varchar(6)
set @年级='2010级'create table #Class (CID int identity,班级 int)
--班级随机：否则“新生数/班级”的余数部分，只能随机分配给前面的班级：
insert #Class(班级) select 班级 from @Grade where 年级=@年级 order by newid()
select @Max=@@rowcountupdate @New set 年级=@年级,班级=c.班级
from @New a
inner join #New b on a.学号=b.学号
inner join #Class c on b.SID%@Max+1=c.CIDselect * from @New
/*
学号          姓名   年级     班级          
----------- ---- ------ ----------- 
111         AA   2010级  4
112         BB   2010级  3
113         CC   2010级  4
120         DD   2010级  2
121         EE   2010级  1
*/drop table #New,#Class