求一个SQL语句

[flyskylf]

现有三个表 T1, T2, T3
T1数据:
F1 (字段名称)
1
2

T2数据:
F1 (字段名称)
A
B
C

T3数据
F1 F2 (字段名称)
1 A
2 A
1 B
2 B
1 C

所需结果:
将 2 C 这一行数据插入T3，就是T1， T2表中数据进行全排列,将数据插入T3，如果T3中已经存在则忽略存在行.不知道我描述的是否清楚.

谢谢各位~

[dawugui]

insert into t3 select * from (select t1.f1 , t2.f1 f2 from t1 , t2) t where not exists(select 1 from t3 where t3.f1 = t.f1 and t3.f2 = t.f2)

[dawugui]

create table T1(F1 int)
insert into t1 values(1)
insert into t1 values(2)
create table t2(F1 varchar(10))
insert into t2 values('A')
insert into t2 values('B')
insert into t2 values('C')
create table t3(F1 int,F2 varchar(10))
insert into t3 values(1 ,'A')
insert into t3 values(2 ,'A')
insert into t3 values(1 ,'B')
insert into t3 values(2 ,'B')
insert into t3 values(1 ,'C')

insert into t3 select * from (select t1.f1 , t2.f1 f2 from t1 , t2) t where not exists(select 1 from t3 where t3.f1 = t.f1 and t3.f2 = t.f2)

select * from t3

drop table t1 , t2 , t3

/*
F1          F2         
----------- ---------- 
1           A
2           A
1           B
2           B
1           C
2           C

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

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-10-29 15:58:34
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[T1]
if object_id('[T1]') is not null drop table [T1]
go 
create table [T1]([F1] int)
insert [T1]
select 1 union all
select 2
--> 测试数据：[T2]
if object_id('[T2]') is not null drop table [T2]
go 
create table [T2]([F1] varchar(1))
insert [T2]
select 'A' union all
select 'B' union all
select 'C'
--> 测试数据：[T3]
if object_id('[T3]') is not null drop table [T3]
go 
create table [T3]([F1] int,[F2] varchar(1))
insert [T3]
select 1,'A' union all
select 2,'A' union all
select 1,'B' union all
select 2,'B' union all
select 1,'C'
--------------开始查询--------------------------
--select *  from t1 cross join  (select t2.f1 f2  from  t2)a
insert into t3 select * from (select *  from t1 cross join  (select t2.f1 f2  from  t2)a) b where not exists(select 1 from t3 where t3.f1 =b.f1 and t3.f2 = b.f2)
select * from t3
----------------结果----------------------------
/* F1          F2
----------- ----
1           A
2           A
1           B
2           B
1           C
2           C

(6 行受影响)
*/

[flyskylf]

谢谢楼上两位大侠,测试通过,并已结帖~

[dawugui]

把我2楼的更改一下，减少一层查询。

create table T1(F1 int)
insert into t1 values(1)
insert into t1 values(2)
create table t2(F1 varchar(10))
insert into t2 values('A')
insert into t2 values('B')
insert into t2 values('C')
create table t3(F1 int,F2 varchar(10))
insert into t3 values(1 ,'A')
insert into t3 values(2 ,'A')
insert into t3 values(1 ,'B')
insert into t3 values(2 ,'B')
insert into t3 values(1 ,'C')

insert into t3 select t1.f1 , t2.f1 f2 from t1 , t2 where not exists(select 1 from t3 where t3.f1 = t1.f1 and t3.f2 = t2.f1)

select * from t3

drop table t1 , t2 , t3

/*
F1          F2         
----------- ---------- 
1           A
2           A
1           B
2           B
1           C
2           C

（所影响的行数为 6 行）

*/