oracle中insert語(yǔ)句怎么嵌入select？

嵌入方式如下。

INSERT INTO target_table (col1, col2, col3)

SELECT col1,

col2,

col3

FROM source_table

WHERE condition;

其中的select可以使用單表，也可以使用多表，分別舉例說(shuō)明如下。

1. select中使用單表查詢(xún)

下面了演示如何使用insert into select語(yǔ)句，首先創(chuàng)建一個(gè)名為sales的表。

CREATE TABLE sales (

customer_id NUMBER,

product_id NUMBER,

order_date DATE NOT NULL,

total NUMBER(9,2) DEFAULT 0 NOT NULL,

PRIMARY KEY(customer_id,

product_id,

order_date)

);

以下語(yǔ)句將orders和order_items表中的銷(xiāo)售摘要插入到sales表中，參考以下實(shí)現(xiàn)語(yǔ)句 -

INSERT INTO sales(customer_id, product_id, order_date, total)

SELECT customer_id,

product_id,

order_date,

SUM(quantity * unit_price) amount

FROM orders

INNER JOIN order_items USING(order_id)

WHERE status = 'Shipped'

GROUP BY customer_id,

product_id,

order_date;

?

2. select中使用多表查詢(xún)

假設(shè)只想將2017年的銷(xiāo)售摘要數(shù)據(jù)復(fù)制到新表中。 為此，創(chuàng)建一個(gè)名為sales_2017的新表，使用Oracle INSERT INTO SELECT和WHERE子句將2017年的銷(xiāo)售數(shù)據(jù)復(fù)制到sales_2017表中：

INSERT INTO sales_2017

SELECT customer_id,

product_id,

order_date,

SUM(quantity * unit_price) amount

FROM orders

INNER JOIN order_items USING(order_id)

WHERE status = 'Shipped' AND EXTRACT(year from order_date) = 2017

GROUP BY customer_id,

product_id,

order_date;

?