研究生做的

[xlh0053]

表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
... ...

[AcHerat]

insert into c
select b.no,a.id,3
from a corss join b
where b.rty = 3

[百年树人]

insert into c
select a.id,b.no,3
from (select distinct id from a) a
join (select distinct no from b where ACTI='Y') b
on 1=1

[叶子]

--> 测试数据: @表A
declare @表A table (col int)
insert into @表A
select 10001 union all
select 10002

declare @表B table (NO int,ACTI varchar(1))
insert into @表B
select 980001,'Y' union all
select 980002,'Y' union all
select 980003,'N'

select col,NO,3 as ACTI from @表A a cross join @表B b  where b.ACTI='Y'

/*
col         NO          ACTI
----------- ----------- -----------
10001       980001      3
10001       980002      3
10002       980001      3
10002       980002      3
*/

[开着拖拉机泡妞]

[Quote=引用 2 楼 josy 的回复:]

SQL code
insert into c
select a.id,b.no,3
from (select distinct id from a) a
join (select distinct no from b where ACTI='Y') b
on 1=1
[/Quote]

这个我也会，能不能也去读研究生

[昵称被占用了]

[xlh0053]

哈哈，鲁迅先生，你太牛了