create table newtablename as select ...from group by ...
SQL> select * from employee;ID FIRST_NAME LAST_NAME START_DATE END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- ----------- ----------- ---------- ---------- --------------- 09 Jason1 Martin2 1996-7-25 2006-7-25 1234.56 Toronto Programmer 10 Alison1 Mathews2 1976-3-21 1986-2-21 6661.78 Vancouver Tester 11 James1 Smith2 1978-12-12 1990-3-15 6544.78 Vancouver Tester 12 Celia1 Rice2 1982-10-24 1999-4-21 2344.78 Vancouver Manager 13 Robert1 Black2 1984-1-15 1998-8-8 2334.78 Vancouver Tester 14 Linda1 Green2 1987-7-30 1996-1-4 4322.78 New York Tester 15 David1 Larry2 1990-12-31 1998-2-12 7897.78 New York Manager 16 James1 Cat2 1996-9-17 2002-4-15 1232.78 Vancouver Tester 01 Jason Martin 1996-7-25 2006-7-25 10000.00 Toronto Programmer 02 Alison Mathews 1976-3-21 1986-2-21 6661.78 Vancouver Tester 03 James Smith 1978-12-12 1990-3-15 6544.78 Vancouver Tester 04 Celia Rice 1982-10-24 1999-4-21 2344.78 Vancouver Manager 05 Robert Black 1984-1-15 1998-8-8 2334.78 Vancouver Tester 06 Linda Green 1987-7-30 1996-1-4 4322.78 New York Tester 07 David Larry 1990-12-31 1998-2-12 7897.78 New York Manager 08 James Cat 1996-9-17 2002-4-15 1232.78 Vancouver Tester16 rows selectedSQL> select city,sum(salary) from employee group by city;CITY SUM(SALARY) ---------- ----------- New York 24441.12 Toronto 11234.56 Vancouver 38237.8SQL> create table test0 as (select tab.t1,tab.t2 from (select city t1,sum(salary) t2 from employee group by city) tab);Table createdSQL> select * from test0;T1 T2 ---------- ---------- New York 24441.12 Toronto 11234.56 Vancouver 38237.8SQL>
insert into tablename1 select ... from tablename2 group by ...
INSERT INTO 表1(表1.字段1,表1.字段2, 表1.字段3)(SELECT 表B.字段1,表B.字段2,表B.字段3 FROM 表B) 如果表1中不光只有3个字段,那其他字段将为空(null)
insert into table_name(f1, f2,....) select f1, f2,.... from tb2 group by ..
也可以,INSERT INTO 表1(表1.字段1,表1.字段2, 表1.字段3)(SELECT 表B.字段1,表B.字段2,表B.字段3 FROM 表B) 红色的是你要接收插入的字段,蓝色是你新产生准备去插入的数据,红色地方的一个字段,对性蓝色地方相因字段的值
---- ---------- ---------- ----------- ----------- ---------- ---------- ---------------
09 Jason1 Martin2 1996-7-25 2006-7-25 1234.56 Toronto Programmer
10 Alison1 Mathews2 1976-3-21 1986-2-21 6661.78 Vancouver Tester
11 James1 Smith2 1978-12-12 1990-3-15 6544.78 Vancouver Tester
12 Celia1 Rice2 1982-10-24 1999-4-21 2344.78 Vancouver Manager
13 Robert1 Black2 1984-1-15 1998-8-8 2334.78 Vancouver Tester
14 Linda1 Green2 1987-7-30 1996-1-4 4322.78 New York Tester
15 David1 Larry2 1990-12-31 1998-2-12 7897.78 New York Manager
16 James1 Cat2 1996-9-17 2002-4-15 1232.78 Vancouver Tester
01 Jason Martin 1996-7-25 2006-7-25 10000.00 Toronto Programmer
02 Alison Mathews 1976-3-21 1986-2-21 6661.78 Vancouver Tester
03 James Smith 1978-12-12 1990-3-15 6544.78 Vancouver Tester
04 Celia Rice 1982-10-24 1999-4-21 2344.78 Vancouver Manager
05 Robert Black 1984-1-15 1998-8-8 2334.78 Vancouver Tester
06 Linda Green 1987-7-30 1996-1-4 4322.78 New York Tester
07 David Larry 1990-12-31 1998-2-12 7897.78 New York Manager
08 James Cat 1996-9-17 2002-4-15 1232.78 Vancouver Tester16 rows selectedSQL> select city,sum(salary) from employee group by city;CITY SUM(SALARY)
---------- -----------
New York 24441.12
Toronto 11234.56
Vancouver 38237.8SQL> create table test0 as (select tab.t1,tab.t2 from (select city t1,sum(salary) t2 from employee group by city) tab);Table createdSQL> select * from test0;T1 T2
---------- ----------
New York 24441.12
Toronto 11234.56
Vancouver 38237.8SQL>
如果表1中不光只有3个字段,那其他字段将为空(null)
select f1, f2,....
from tb2 group by ..
红色的是你要接收插入的字段,蓝色是你新产生准备去插入的数据,红色地方的一个字段,对性蓝色地方相因字段的值