postgresql—-排序ORDER BY，分组GROUP BY，分页OFFSET&&LIMIT详解数据库

使用GROUP BY分组查询在SELECT子句中只能出现分组字段和聚合函数，HAVING子句相当于WHERE，使用条件过滤数据。

示例1.以a,b分组查询tbl_insert表，且a大于5的行。

test=# select a,b,count(*) from tbl_insert group by a,b having a 5; 

 a | b | count 

---+---+------- 

 7 | 7 | 4 

 8 | 8 | 2 

 6 | 6 | 3 

(3 rows) 

test=# select a,b,count(*) from tbl_insert where a 5 group by a,b; 

 a | b | count 

---+---+------- 

 7 | 7 | 4 

 8 | 8 | 2 

 6 | 6 | 3 

(3 rows)

示例2.以a,b分组查询tbl_insert表，且a,b组合相同的行数大于2的a,b组合

test=# select a,b,count(*) from tbl_insert group by a,b having count(*) 2; 

 a | b | count 

---+---+------- 

 7 | 7 | 4 

 6 | 6 | 3 

(2 rows)

示例3.以a,b分组查询tbl_insert表，如果SELECT子句中包含其他字段，则ERROR。

test=# select a,b,c from tbl_insert group by a,b; 

ERROR: column "tbl_insert.c" must appear in the GROUP BY clause or be used in an aggregate function 

LINE 1: select a,b,c from tbl_insert group by a,b;

使用ORDER BY对结果集进行排序，默认使用ASC升序排列，可指定DESC降序排列。

示例1.查询tbl_insert表按照a升序,c降序排列

test=# select * from tbl_insert where a 6 order by a ,c desc; 

 a | b | c 

---+---+------- 

 7 | 7 | abc 

 7 | 7 | aBC 

 7 | 7 | ABc 

 7 | 7 | 3%1 

 8 | 8 | 3_%_1 

 8 | 8 | 3%_1 

(6 rows)

示例2.以字段a,b分组查询tbl_insert表，并按照各分组中行数降序排列。

test=# select a,b,count(*) from tbl_insert group by a,b order by count(*) desc; 

 a | b | count 

---+---+------- 

 7 | 7 | 4 

 6 | 6 | 3 

 8 | 8 | 2 

 4 | 4 | 1 

 5 | 5 | 1 

 1 | 1 | 1 

 3 | 3 | 1 

 2 | 2 | 1 

(8 rows)

使用LIMIT可以限制结果集的行数。

示例1.查询tbl_insert表，并以a降序排列，只输出前5行数据。

test=# select * from tbl_insert order by a desc limit 5; 

 a | b | c 

---+---+------- 

 8 | 8 | 3_%_1 

 8 | 8 | 3%_1 

 7 | 7 | 3%1 

 7 | 7 | ABc 

 7 | 7 | abc 

(5 rows)

使用OFFSET可以忽略前面的N行，与LIMIT结合使用可以实现分页功能。

示例1.查询tbl_insert表，并以a升序排列，前10行忽略不输出。

test=# select * from tbl_insert order by a offset 10; 

 a | b | c 

---+---+------- 

 7 | 7 | ABc 

 7 | 7 | aBC 

 8 | 8 | 3%_1 

 8 | 8 | 3_%_1 

(4 rows)

示例2.查询tbl_insert表，并以a升序排列，实现分页功能，每页5行数据。

test=# select * from tbl_insert order by a offset 0 limit 5; 

 a | b | c 

---+---+---- 

 1 | 1 | 11 

 2 | 2 | 22 

 3 | 3 | 33 

 4 | 4 | 44 

 5 | 5 | 51 

(5 rows) 

test=# select * from tbl_insert order by a offset 5 limit 5; 

 a | b | c 

---+---+----- 

 6 | 6 | 661 

 6 | 6 | 1 

 6 | 6 | 61 

 7 | 7 | 3%1 

 7 | 7 | abc 

(5 rows) 

test=# select * from tbl_insert order by a offset 10 limit 5; 

 a | b | c 

---+---+------- 

 7 | 7 | ABc 

 7 | 7 | aBC 

 8 | 8 | 3%_1 

 8 | 8 | 3_%_1 

(4 rows)

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/4856.html