用SQL语句分离一个表里的记录的问题,高手帮忙指点一下!急!!

[lianhoo]

问题是这样的:在一个表sheet里(有10000条记录),形式如下:

id expr1 mail
1 uakron.edu [e-mail]
2 uakron.edu [e-mail]
3 uakron.edu [e-mail]
4 uakron.edu [e-mail]
5 adrian.edu [e-mail]
6 adelphi.edu [e-mail]
7 adelphi.edu [e-mail]
.... ..... .........

要求将这个表中的expr1中的每三个相同的记录(但mial不能相同)与另外expr1三个相同的记录(整合到一个表中,把这个表分离成多个表,也就是说每个表中最多有三个expr1相同的记录存在.
算法好像是用循环,每次循环分离出一个表来,mail里的每个记录只能在一个表中,不能在多个表中.

先谢谢大家了!

[-狙击手-]

declare @t table(id int,expr1 varchar(20),mail varchar(30))
insert @t
select 1,'uakron.edu','[e-mail]' union all
select 2,'uakron.edu','[e-mail]' union all
select 3,'uakron.edu','[e-mail]' union all
select 4,'uakron.edu','[e-mail]' union all
select 5,'adrian.edu','[e-mail]' union all
select 6,'adelphi.edu','[e-mail]' union all
select 7,'adelphi.edu','[e-mail]'


select rid = (select count(1)+ 1 from @t where expr1 = a.expr1 and id < a.id)
,* into #
from @t a

select * from #
declare @I int
set @I = 1
declare @j int
set @j = 1
declare @sql varchar(8000)
declare @count int
declare @counttemp int
set @counttemp = 0
select @count = count(*) from #
while @count > @counttemp
begin
set @sql = 'select * into tempexpr'+convert(varchar,@j)+ ' from # where rid >= '+cast(@i as varchar)
+' and rid <' +cast(@I as varchar)+' + 3'
print @sql
exec(@sql)
select @counttemp = @counttemp + @@rowcount
select @I = @i + 3
set @j = @j + 1

end
select * from tempexpr1
select * from tempexpr2
select * from tempexpr3 ---根据情况select不同表


drop table #,tempexpr1,tempexpr2,tempexpr3,tempexpr4,tempexpr5,tempexpr6
---根据情况drop不同表

[-狙击手-]

declare @t table(id int,expr1 varchar(20),mail varchar(30))
insert @t
select 1,'uakron.edu','[e-mail]' union all
select 2,'uakron.edu','[e-mail]' union all
select 3,'uakron.edu','[e-mail]' union all
select 4,'uakron.edu','[e-mail]' union all
select 5,'adrian.edu','[e-mail]' union all
select 6,'adelphi.edu','[e-mail]' union all
select 7,'adelphi.edu','[e-mail]'


select rid = (select count(1)+ 1 from @t where expr1 = a.expr1 and id < a.id)
,* into #
from @t a

select * from #
declare @I int
set @I = 1
declare @j int
set @j = 1
declare @sql varchar(8000)
declare @count int
declare @counttemp int
set @counttemp = 0
select @count = count(*) from #
while @count > @counttemp
begin
set @sql = 'select * into tempexpr'+convert(varchar,@j)+ ' from # where rid >= '+cast(@i as varchar)
+' and rid <' +cast(@I as varchar)+' + 3'
print @sql
exec(@sql)
select @counttemp = @counttemp + @@rowcount
select @I = @i + 3
set @j = @j + 1

end
select * from tempexpr1
select * from tempexpr2
select * from tempexpr3 ---根据情况select不同表


drop table #,tempexpr1,tempexpr2,tempexpr3,tempexpr4,tempexpr5,tempexpr6
---根据情况drop不同表

[lianhoo]

高手啊!!
可是我还不太明白.
小弟刚学.有简单点的方法吗?
谢谢

[lianhoo]

技术搞定了,下面是源码,也给后来的人看看吧.同时谢谢一楼的朋友!
declare @i int,@n int,@count int ,@max int , @name_next varchar(50),@name_now varchar(50),@nameid int,@mail varchar(50)
declare @tablename varchar(10),@sqlCreate varchar(1000),@sqlInsert varchar(1000)

SELECT @max=MAX(max_expr1) FROM (SELECT COUNT(expr1) AS max_expr1 FROM aa GROUP BY expr1) DERIVEDTBL


set @n=1
update aa set flag=1
while @n<=(@max/3)+1
begin
set @count=0
set @tablename = 'table' + convert(varchar(10),@n)
set @sqlCreate = 'CREATE TABLE ' + @tablename + '(mail varchar(50) )'
print(@sqlCreate)
EXECUTE(@sqlCreate)
set @i=1
while @i<=15961
begin
select @nameid=flag from aa where id=@i
if (@nameid!=0)
begin
select @name_now=expr1 from aa where id=@i

select @mail=mail from aa where id=@i
select @name_next =expr1 from aa where id =@i+1

if (@count<3) --and @nameid=1@name_next=@name_now and
begin
--insert @tablename (mail) values (@mail) --where id=i

set @sqlInsert='insert into ' + @tablename + ' (mail) ' + ' values ('''+@mail+''' )'
print(@sqlInsert)
execute(@sqlInsert)
update aa set flag=0 where id =@i
if(@name_next=@name_now ) set @count=@count+1
end
else
begin
if(@name_next!=@name_now) set @count=0
end
end
set @i=@i+1
end

set @n=@n+1
end