SQL问题求救！！ - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

SQL问题求救！！

SQL问题求救！！# Database - 数据库

l*y2000-11-12 08:11

1 楼

本人有简单问题求教
已通过查询获得若干个结果，现必须将这些结果insert
到另一个表，say: result(number,value1,value2)
现在问题是：
只有value1 和value2是已知道的(即前面的结果)。可是
number 是一个primary key,表示序号如1,2,3,4,5,....
请问怎么样写insert into result(number,value1,value2)..???
我的意思是有100个value1,value组成的tuple.如何得到
table result such as
number value1 value 2
1 x1 y1
2 x2 y2
.....................
100 x100 y100
我尝试用

insert into result(number,value1,value2)
(
select number,A.value1,A,value2
from A
)
但是如何累加num

s*g2000-11-12 08:11

2 楼

In the result table, you may need an identity.
CREATE TABLE RESULT
(id int identity(1,1) not null,
value1 datatype,
value2 datatype
)
Then you can insert the values of value1 and value21.
INSERT INTO RESULT(value1, value2)
SELECT value1,value2
FROM A
Hope that it works.

【在 l*****y 的大作中提到】

: 本人有简单问题求教
: 已通过查询获得若干个结果，现必须将这些结果insert
: 到另一个表，say: result(number,value1,value2)
: 现在问题是：
: 只有value1 和value2是已知道的(即前面的结果)。可是
: number 是一个primary key,表示序号如1,2,3,4,5,....
: 请问怎么样写insert into result(number,value1,value2)..???
: 我的意思是有100个value1,value组成的tuple.如何得到
: table result such as
: number value1 value 2

s*g2000-11-12 08:11

3 楼

BTW, in the function identity(1,1), you have the id++ everytime you
have a new record. So you don't have to insert values for this attribute.

【在 s*****g 的大作中提到】

: In the result table, you may need an identity.
: CREATE TABLE RESULT
: (id int identity(1,1) not null,
: value1 datatype,
: value2 datatype
: )
: Then you can insert the values of value1 and value21.
: INSERT INTO RESULT(value1, value2)
: SELECT value1,value2
: FROM A

p*s2000-11-12 08:11

4 楼

In Oracle, it is quite easy to achieve this by using SEQUENCE.
E.g.,
CREATE SEQUENCE my_sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE;
INSERT INTO result(number,value1,value2)
select my_sequence.nextval, A.value1,A,value2
from a;
DROP SEQUENCE my_sequence.
In DB2, you must use user-defined function with scratchpad (check
Chamberline's book for coding examples) to achieve the same effects.

【在 l*****y 的大作中提到】