while @i<5
begin
select top 1 @lastID=id, @friend=src_num
from sms.dbo.tbl_lt_order where id <>@lastID order by newid()
if exists(select * from sms.dbo.tbl_friend
where src_id!=@src_id and friend!=@friend)
end
begin
select top 1 @lastID=id, @friend=src_num
from sms.dbo.tbl_lt_order where id <>@lastID order by newid()
if exists(select * from sms.dbo.tbl_friend
where src_id!=@src_id and friend!=@friend)
end
declare #aa cousor for select top 5 src_num from sms.dbo.tbl_lt_order order by newid()
open #aa
fetch next from #aa into @friend
while @@fetch_status=0
begin
if exists(select * from sms.dbo.tbl_friend
where src_id!=@src_id and friend!=@friend)
fetch next from #aa into @friend
end
close #aa
deallocate #aa
begin
select top 1 @lastID=id, @friend=src_num
from sms.dbo.tbl_lt_order where id <>@lastID order by newid()
if exists(select * from sms.dbo.tbl_friend
where src_id!=@src_id and friend!=@friend)
end
begin
select top 1 @friend=src_num
from (select * from sms.dbo.tbl_lt_order group by 所有字段) as aa order by newid()
if exists(select * from sms.dbo.tbl_friend
where src_id!=@src_id and friend!=@friend)
end
我那个是对表做了5次扫描,每次只要不是@lastid的话,还是可能出现重复的记录,并且不能够取到最后的那一条记录。
在表里加一个字段,INT 就可以了,已经抽取过的记录,打上标记,以后就不会在抽到重复的了
用游标确实是个好的方法,不过当有相同记录时,可能取不到五条记录,我想这个应该比较好控制,谢谢。
declare #aa cursor for select distinct top 5 src_num from sms.dbo.tbl_lt_order order by newid()
open #aa
fetch next from #aa into @friend
while @@fetch_status=0
begin
if exists(select * from sms.dbo.tbl_friend
where src_id!=@src_id and friend!=@friend)
fetch next from #aa into @friend
end
close #aa
deallocate #aa
declare #aa cursor for select distinct top 5 src_num from(select top 100 percent src_num from sms.dbo.tbl_lt_order order by newid()) a
open #aa
fetch next from #aa into @friend
while @@fetch_status=0
begin
if exists(select * from sms.dbo.tbl_friend
where src_id!=@src_id and friend!=@friend)
fetch next from #aa into @friend
end
close #aa
deallocate #aa
declare #aa cursor for select top 20 src_num from sms.dbo.tbl_lt_order order by newid()
open #aa
fetch next from #aa into @friend
while @@fetch_status=0
begin
while @i<5
begin
if exists(select * from sms.dbo.tbl_friend where src_id=@src_id and friend=@friend)
print @i
else
begin
set @msg_text2=@msg_text2+','+(CAST(@friend AS varchar(10)))
set @i=@i+1
end
fetch next from #aa into @friend
end fetch next from #aa into @friend
end
close #aa
deallocate #aa