请教高手：全部分奉上！！！！！
表1
学校 姓名
002 张三
002 李桑
002 王大
002 赵2
......
001 A1
001 ZHAN
001 陆家
......
003 甲
003 丙
003 乙
.........
结果表
序号 学校 姓名
1 001 陆家
2 002 李桑
3 003 丙
4 001 A1
5 002 张三
6 003 甲
.........
30 xxx xxx
1 xxx xxx
2 xxx xxx
....
30 xxx xxx
1 xxx xxx
2 xxx xxx
......
30 xxx xxx
学校、学生随机取，一组每家学校取一名学生排好（每家学校都要取1名学生），30人为一组排好后取下一组，序号都是1至30，剩余的有几人算几人（学校、学生对应，不要重复取），直到所有学校取完为止，存入另一个数据库。（用于APS.NTE,不用LINQ，我不会）谢谢。 数据库
[解决办法]
--試試以下：
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [学校] varchar(100), [姓名] varchar(100));
insert #temp
select '002','张三' union all
select '002','李桑' union all
select '002','王大' union all
select '002','赵2' union all
select '001','A1' union all
select '001','ZHAN' union all
select '001','陆家' union all
select '003','甲' union all
select '003','丙' union all
select '003','乙'
;WITH a1 AS
(
select rowid1 = ROW_NUMBER() OVER(PARTITION BY 学校 ORDER BY NEWID()),* from #temp
)
,a2 AS
(
select rowid2 = ROW_NUMBER() OVER(PARTITION BY rowid1 ORDER BY NEWID()),* from a1
)
select rowid = ROW_NUMBER() OVER(PARTITION BY rowid1,(rowid2-1)/30 ORDER BY NEWID()),[学校],[姓名]
from a2
if OBJECT_ID('tb') is not null
drop table tb;
go
create table tb( [学校] varchar(100), [姓名] varchar(100));
insert tb
select '002','张三' union all
select '002','李桑' union all
select '002','王大' union all
select '002','赵2' union all
select '001','A1' union all
select '001','ZHAN' union all
select '001','陆家' union all
select '003','甲' union all
select '003','丙' union all
select '003','乙' union all
select '002','张三2' union all
select '002','李桑2' union all
select '002','王大2' union all
select '002','赵22' union all
select '001','A12' union all
select '001','ZHAN2' union all
select '001','陆家2' union all
select '003','甲2' union all
select '003','丙2' union all
select '003','乙2' union all
select '002','张三3' union all
select '002','李桑3' union all
select '002','王大3' union all
select '002','赵23' union all
select '001','A13' union all
select '001','ZHAN3' union all
select '001','陆家3' union all
select '003','甲3' union all
select '003','丙3' union all
select '003','乙3' union all
select '001','陆家123' union all
select '003','甲123' union all
select '001','陆家1234' union all
select '003','甲1234' union all
select '002','张三' union all
select '002','李桑' union all
select '002','王大' union all
select '002','赵2' union all
select '001','A1' union all
select '001','ZHAN' union all
select '001','陆家' union all
select '003','甲' union all
select '003','丙' union all
select '003','乙' union all
select '002','张三2' union all
select '002','李桑2' union all
select '002','王大2' union all
select '001','A12' union all
select '001','ZHAN2' union all
select '001','陆家2' union all
select '003','甲2' union all
select '003','丙2' union all
select '003','乙2' union all
select '002','张三3' union all
select '002','李桑3' union all
select '002','王大3' union all
select '002','赵23' union all
select '001','A13' union all
select '001','ZHAN3' union all
select '001','陆家3' union all
select '003','甲3' union all
select '003','丙3' union all
select '003','乙3' union all
select '001','陆家5' union all
select '003','甲5' union all
select '001','陆家5' union all
select '003','甲6'
;with t
as
(
select 学校,姓名,
row_number() over(partition by 学校 order by @@servername) as rownum
from tb
)
select
case when (row_number() over(order by @@servername)) % 30 = 0
then 30
else (row_number() over(order by @@servername)) % 30
end as r,
学校,
姓名
from t
order by rownum,学校
/*
r学校姓名
1001A1
2002张三2
3003甲
4001ZHAN
5002李桑2
6003丙
7001陆家
8002王大2
9003乙
10001A12
11002张三3
12003甲2
13001ZHAN2
14002李桑3
15003丙2
16001陆家2
17002王大3
18003乙2
19001A13
20002赵23
21003甲3
22001ZHAN3
23002张三
24003丙3
25001陆家3
26002李桑
27003乙3
28001陆家123
29002王大
30003甲1234
1001陆家1234
2002赵2
3003甲123
4001A1
5002张三3
6003甲3
7001ZHAN
8002李桑3
9003丙3
10001陆家
11002王大3
12003乙3
13001A12
14002赵23
15003甲2
16001ZHAN2
17002张三2
18003丙2
19001陆家2
20002李桑2
21003乙2
22001A13
23002王大2
24003甲
25001ZHAN3
26002赵22
27003丙
28001陆家3
29002张三
30003乙
1001陆家5
2002李桑
3003甲5
4001陆家5
5002王大
6003甲6
7002赵2
*/
