求条超难得sql语句

[hackering]

表1：
user aa
a 1
b 2
c 3
....
表2：
cap bb
x1 1
x2 2
x3 3
x4 4
....
表3是有三个字段 user cap mult
b x2 25
c x1 100

表1.user，表2.cap都是没有重复的字段
表3中所有字段都可以重复
==================================================
要求补充表3
计算每一个user,对应每一个cap,且mult=aa*bb
等同于计算出乘法口诀表
[注意，如果表3已经有了此项，则不准补充，没有则需要补充！]
即上例中要得到如下的结果：
user cap mult
a x1 1
a x2 25
a x3 3
a x4 4
b x1 2
b x2 4
b x3 6
b x4 8
c x1 100
c x2 6
c x3 9
c x4 12

**************************************
求sql语句写法！拜谢了先

在线等

[昵称被占用了]

select a.user,b.cap,a.aa*b.bb as mult
from 表1 a,表2 b

[昵称被占用了]

理解有点偏差

insert 表3(user,cap,mult)
select a.user,b.cap,a.aa*b.bb as mult
from 表1 a,表2 b
where not exists (
select 1 from 表3
where user=a.user
and cap=b.cap
)

[xeqtr1982]

declare @t table([user] varchar(10),aa int)
insert into @t select 'a',1
union all select 'b',2
union all select 'c',3

declare @a table(cap varchar(10),bb int)
insert into @a select 'x1',1
union all select 'x2',2
union all select 'x3',3
union all select 'x4',4

declare @b table([user] varchar(10),cap varchar(10),mult int)
insert into @b select 'b','x2',25
union all select 'c','x1',100

insert into @b select a.[user],b.cap,aa*bb as mult from @t a,@a b where not exists(select 1 from @b where [user]=a.[user] and cap=b.cap)

select * from @b order by [user],cap

[xeqtr1982]

不过楼主的结果好象有点问题，25应该是b x2 的mult值吧

[dssw]

要想得到搂主的结果
核心：select a.[user],b.[cap] from 表1 a ,表2 b order by a.[user],b.[cap]
要满足 [注意，如果表3已经有了此项，则不准补充，没有则需要补充！这个要求
select a.[user],b.[cap] from 表1 a ,表2 b
where not exists(select 1 from 表3 c where c.[user]=a.[user] and c.[cap]= b.[cap] )
order by a.[user],b.[cap]

[dssw]

修改：
要想得到搂主的结果
核心：select a.[user],b.[cap],a.[aa]* b.[bb] as [mult] from 表1 a ,表2 b order by a.[user],b.[cap]
要满足 [注意，如果表3已经有了此项，则不准补充，没有则需要补充！这个要求
select a.[user],b.[cap] ,a.[aa]* b.[bb] as [mult] from 表1 a ,表2 b
where not exists(select 1 from 表3 c where c.[user]=a.[user] and c.[cap]= b.[cap] )
order by a.[user],b.[cap]

[rouqu]

select a.user,b.cap,a.aa*b.bb as mult
from 表1 a,表2 b
union
select * from 表3

[rouqu]

两表纪录没有匹配条件 根据我的理解 该是笛卡儿乘积