如何通过sql实现这样的操作

[rehearts]

TableA
CompanyID


TableB
ProductID


TableC
ID
CompanyID
ProductID

现在要为TableA中的每个公司 从TableB里面随机选出20条产品,
然后写如到TableC中,这个SQL怎么写呢

(每个公司对应的产品都是随机挑的)

[dawugui]

你tableB少个字段吧?应该少个companyid

[playwarcraft]

declare @companyID int
declare c1 cursor for
select companyID from TableA
open c1
fetch next from c1 into @companyID
while @@fetch_status=0
begin
insert into TableC(CompanyID,ProductID)
select top 20 @companyID,ProductID
from TableB
order by newid()

fetch next from c1 into @companyID
end
close c1
deallocate c1

[gahade]

declare @CompanyID int
declare cur_tmp cursor for
select CompanyID from TableA
open cur_tmp
fetch next from cur_tmp into @CompanyID
while @@fetch_status = 0
begin
	insert into TableC(CompanyID,ProductID)
	select top 20 @CompanyID,ProductID from TableB order by newid()
	fetch next from cur_tmp into @CompanyID
end
close cur_tmp
deallocate cur_tmp

[dawugui]

--查询,你C表的ID如何处理?

select * from 
(
  select b.* from tablea a,tableb b where a.companyid = b.companyid
) t
where ProductID in 
(
  select top 20 ProductID from
  (
    select b.* from tablea a,tableb b where a.companyid = b.companyid
  ) m 
  where companyid = t.companyid
  order by newid()
)

[gahade]

好快,:)

[rehearts]

TableB 中没有ProductID, 每个公司随机找几个产品,然后写入到TableC中

[dawugui]

create table A(CompanyID varchar(20))
insert into A values('CompanyID01')   
insert into A values('CompanyID02')   
create table B(ProductID varchar(20))
insert into B values('ProductID01')
insert into B values('ProductID02')
insert into B values('ProductID03')
insert into B values('ProductID04')
insert into B values('ProductID05')
insert into B values('ProductID06')
insert into B values('ProductID07')
insert into B values('ProductID08')
insert into B values('ProductID09')
insert into B values('ProductID10')
insert into B values('ProductID11')
insert into B values('ProductID12')
insert into B values('ProductID13')
insert into B values('ProductID14')
insert into B values('ProductID15')
go

--A表数量
declare @cntA as int
select @cntA = count(*) from A

--5 * A的数量(你将其改为20)
declare @numB as int
declare @sql as varchar(2000)
set @numB = 5 * @cntA
set @sql = 'select top ' + cast(@numB as char) + ' * into tmp from B order by newid()'
exec(@sql)

select t1.CompanyID , t2.ProductID from
(SELECT * , px=(SELECT COUNT(CompanyID) FROM A WHERE CompanyID < t.CompanyID) + 1 FROM A t) t1,
( 
  select *,px1 = px%@cntA from
  (
    SELECT * , px=(SELECT COUNT(ProductID) FROM tmp WHERE ProductID < t.ProductID) + 1 FROM tmp t 
  ) m
) t2
where t1.px = t2.px1 + 1
order by t1.CompanyID , t2.ProductID

drop table A,B,tmp

/*
CompanyID            ProductID            
-------------------- -------------------- 
CompanyID01          ProductID02
CompanyID01          ProductID05
CompanyID01          ProductID08
CompanyID01          ProductID12
CompanyID01          ProductID15
CompanyID02          ProductID01
CompanyID02          ProductID04
CompanyID02          ProductID06
CompanyID02          ProductID10
CompanyID02          ProductID14

（所影响的行数为 10 行）
*/