【答案公布】TOP谓词练习2-SQL随机平均分组

wuxiang_123

说明:
名单工作表中有60位学生,现在需要将这60位学生进行随机分组,每组人数15人.最终结果如效果工作表的A:B两列.
要求:
1:不允许使用辅助列.
2:能且仅能在OLE DB中完成.
3:EXCEL版本不限.

我的答案:

游客，如果您要查看本帖隐藏内容请回复

效果:





该贴已经同步到 wuxiang_123的微博

dongdongdo

1. select * from
   
2. (select top 15 *,"1组" as 分组 from [名单$]
   
3. union all
   
4. select top 15 *,"2组" from [名单$a16:a]
   
5. union all
   
6. select top 15 *,"3组" from [名单$a31:a]
   
7. union all
   
8. select top 15 *,"4组" from [名单$a46:a])
   
9. order by 分组,Rnd(len(学生))&学生
   

复制代码

SQL随机平均分组.rar (6.81 KB, 下载次数: 52)

2012-6-29 20:31 上传

点击文件名下载附件

wyf22006

1. select * from (select 学生,"1组" as 组号 from (select 学生,dsum("1","名单$","学生<='"&学生&"'") as 序 from [名单$]) where 序<=15  union all select 学生,"2组" as 组号 from (select 学生,dsum("1","名单$","学生<='"&学生&"'") as 序 from [名单$]) where 序 > 15 and 序 <=30 union all select 学生,"3组" as 组号 from (select 学生,dsum("1","名单$","学生<='"&学生&"'") as 序 from [名单$]) where 序  > 30 and 序 <=45 union all select 学生,"4组" as 组号 from (select 学生,dsum("1","名单$","学生<='"&学生&"'") as 序 from [名单$]) where 序  > 45 and 序 <=60) ORDER BY 组号,rnd(len(学生))

复制代码

wuxiang_123

wyf22006 发表于 2012-6-30 11:13

3楼语句可以简化为:

1. select 学生,int((dsum("1","名单$","学生<='"&学生&"'")-1)/15)+1&'组' as 组号 from [名单$]ORDER BY 2,rnd(len(学生))

复制代码

1109397703

1. SELECT TOP 15 学生,"1组" as 分组 FROM [名单$] ORDER BY RND(LEN(学生)) union all
   
2. SELECT TOP 15 学生,"2组" as 分组 FROM [名单$] where 学生 not in (SELECT TOP 15 学生 FROM [名单$] ORDER BY RND(LEN(学生)))
   
3. union all
   
4. SELECT TOP 15 学生,"3组" as 分组 FROM [名单$] where 学生 not in (SELECT TOP 30 学生 FROM [名单$] ORDER BY RND(LEN(学生)))
   
5. union all
   
6. SELECT TOP 15 学生,"4组" as 分组 FROM [名单$] where 学生 not in (SELECT TOP 45 学生 FROM [名单$] ORDER BY RND(LEN(学生)))
   

复制代码

SQL随机平均分组-1109397703.rar (6.25 KB, 下载次数: 30)

2012-6-30 22:42 上传

点击文件名下载附件

Ｍａs_T_ｅr

看看最终结果是什么呢

huoxieshen

哎学习香帅的解法，实在想不出，呵呵

zhenghui13

做不出来，看结果.

魂断蓝桥

想知道答案

xiaofx11

回复看答案