sql语句insert into问题？

[xwt799023]

现在有三张表，
每一张表名是a1,字段如下：
a1_id a1_name
1 aa
2 bb
3 cc

第二张表名为 a2 ,字段如下：
a2_id a2_name
1 str1
2 str2
3 str3
_______________________
现在想实现往第三表里插入数据,就是把a1表中a1_name这一列与a2表中的a2_name这一列插入到a3表中，实现格式如下：
a3表
a3_id a3_con a3_name
1 aa str1
2 aa str2
3 aa str3
4 bb str1
5 bb str2
6 bb str3
7 cc str1
8 cc str2
9 cc str3

也就是a1表中的a1_name里的一个值对应a2表中的a2_name里的所有值同时插入到a3表中,而且a3表的a3_id自动加1，请问一条sql能写下来吗，如何写呢，多谢各位

[xwt799023]

自已顶下吧

[gw_delphi]

insert into a3(a3_id,a3_con,a3_name)
(
(select nvl(max(a3_id),0) id),
select a1.a1_name,a2.a2_name from a1,a2 where )

[gw_delphi]

insert into a3(a3_id,a3_con,a3_name)
(
(select (nvl(max(id),0) + 1) id from a3),
select a1.a1_name,a2.a2_name from a1,a2
)

[dudongxiao]

insert into a3(a3_id,a3_con,a3_name)
select rownum, a1.a1_name, a2.a2_name from a1, a2

[xwt799023]

to:gw_delphi
提示:ORA-00907: 缺少右括号

[gw_delphi]

insert into a3(a3_id,a3_con,a3_name)
select a1.a1_name,a2.a2_name,a3.id+rownum
from a1,a2，(select (nvl(max(id),0)) id from a3) a3

[gw_delphi]

这样也应该可以
insert into a3(a3_id,a3_con,a3_name)
(
select (select (nvl(max(id),0) + 1) id from a3),a1.a1_name,a2.a2_name
from a1,a2
)

[luoqianqian]

a3的id就用常用的sequnce和trigger来实现自增；然后利用下面的SQL语句
insert into a3(a3_con,a3_name) (select a1_name,a2_name from a1,a2)；最后的括弧里面就是一个广义迪卡尔集

[luoqianqian]

a3的id就用常用的sequnce和trigger来实现自增；这样做可以避免并发问题，在多用户状态下；方法很简单，上网查一下就能找到