日期:2014-05-17 浏览次数:20598 次
--試試以下:
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