PostgreSQL simple select(group by and insert into ...select)

標籤：

 

warehouse_db=# create table student(number int primary key,name varchar(20),age int);
CREATE TABLE
warehouse_db=# insert into student values (1,‘David‘,20);
INSERT 0 1
warehouse_db=# insert into student values (2,‘Luna‘,21);
INSERT 0 1
warehouse_db=# insert into student values (3,‘Lisa‘,21);
INSERT 0 1
warehouse_db=# insert into student values (4,‘Susan‘,22);
INSERT 0 1
warehouse_db=# insert into student values (5,‘Jack‘,22);
INSERT 0 1
warehouse_db=# insert into student values (6,‘Peter‘,23);
INSERT 0 1
warehouse_db=# insert into student values (7,‘Andrew‘,23);
INSERT 0 1
warehouse_db=# insert into student values (8,‘Stallman‘,23);
INSERT 0 1

warehouse_db=# select * from student ;
number | name | age
--------+----------+-----
1 | David | 20
2 | Luna | 21
3 | Lisa | 21
4 | Susan | 22
5 | Jack | 22
6 | Peter | 23
7 | Andrew | 23
8 | Stallman | 23
(8 rows)
分組查詢
warehouse_db=# select age,count(*) from student group by age;
age | count
-----+-------
23 | 3
20 | 1
22 | 2
21 | 2
(4 rows)

warehouse_db=# select * from student order by age;
number | name | age
--------+----------+-----
1 | David | 20
2 | Luna | 21
3 | Lisa | 21
4 | Susan | 22
5 | Jack | 22
6 | Peter | 23
7 | Andrew | 23
8 | Stallman | 23
(8 rows)

warehouse_db=# select * from student order by age desc;
number | name | age
--------+----------+-----
8 | Stallman | 23
6 | Peter | 23
7 | Andrew | 23
5 | Jack | 22
4 | Susan | 22
3 | Lisa | 21
2 | Luna | 21
1 | David | 20
(8 rows)

warehouse_db=# select * from student where age <= 22 order by age desc;
number | name | age
--------+-------+-----
4 | Susan | 22
5 | Jack | 22
2 | Luna | 21
3 | Lisa | 21
1 | David | 20
(5 rows)

 

warehouse_db=# create table student_bak(number int primary key,name varchar(20),age int);
CREATE TABLE
warehouse_db=# insert into student_bak select * from student;
INSERT 0 8
warehouse_db=# select * from student_bak ;
number | name | age
--------+----------+-----
1 | David | 20
2 | Luna | 21
3 | Lisa | 21
4 | Susan | 22
5 | Jack | 22
6 | Peter | 23
7 | Andrew | 23
8 | Stallman | 23
(8 rows)

 

PostgreSQL simple select(group by and insert into ...select)