研究生做的解决办法

研究生做的
表A：ID 
  10001
  10002
  .
  .
  .
  10100
表B：NO ACTI
  980001 Y
  980002 Y
  980003 N
  .
  .
  .
  980100 Y
现在要根据这两个表把B表中字段ACTI为Y的增加到表C中去，表C的结果为
表C：NO ID RTY
  980001 10001 3
  980001 10002 3
  980001 10003 3
  ... ... 3
  980001 10100 3
  980002 10001 3
  980002 10002 3
  980002 10003 3
  ... ... 3
  980002 10100 3
  980004 10001 3
  980004 10002 3
  980004 10003 3
  ... ... 3
  980004 10100 3
  ... ...
 

------解决方案--------------------

SQL code

insert into cselect b.no,a.id,3from a corss join bwhere b.rty = 3
------解决方案--------------------
SQL code
insert into cselect a.id,b.no,3from (select distinct id from a) ajoin (select distinct no from b where ACTI='Y') bon 1=1
------解决方案--------------------
SQL code
--> 测试数据: @表Adeclare @表A table (col int)insert into @表Aselect 10001 union allselect 10002declare @表B table (NO int,ACTI varchar(1))insert into @表Bselect 980001,'Y' union allselect 980002,'Y' union allselect 980003,'N'select col,NO,3 as ACTI from @表A a cross join @表B b  where b.ACTI='Y'/*col         NO          ACTI----------- ----------- -----------10001       980001      310001       980002      310002       980001      310002       980002      3*/