SELECT f.title, f.did, d.name, f.date_prod, f.kind
FROM distributors d, films f
WHERE f.did = d.did
title | did | name | date_prod | kind
-------------------+-----+--------------+------------+----------
The Third Man | 101 | British Lion | 1949-12-23 | Drama
The African Queen | 101 | British Lion | 1951-08-11 | Romantic
统计用kind分组的每组电影的长度len总和:
SELECT kind, sum(len) AS total FROM films GROUP BY kind;
kind | total
----------+-------
Action | 07:34
Comedy | 02:58
Drama | 14:28
Musical | 06:42
Romantic | 04:38
统计用kind分组的每组电影的长度len总和不足五小时的组:
SELECT kind, sum(len) AS total
FROM films
GROUP BY kind
HAVING sum(len) < interval '5 hours';
kind | total
----------+-------
Comedy | 02:58
Romantic | 04:38
下面两个例子是根据第二列(name)的内容对单独的结果排序的相同的方法:
SELECT * FROM distributors ORDER BY name;
SELECT * FROM distributors ORDER BY 2;
did | name
-----+------------------
109 | 20th Century Fox
110 | Bavaria Atelier
101 | British Lion
107 | Columbia
102 | Jean Luc Godard
113 | Luso films
104 | Mosfilm
103 | Paramount
106 | Toho
105 | United Artists
111 | Walt Disney
112 | Warner Bros.
108 | Westward
下面这个例子演示如何获得表distributors和actors的连接,
只将每个表中以字母 W 开头的取出来。因为只取了不重复的行,所以关键字ALL被省略了:
distributors: actors:
did | name id | name
-----+-------------- ----+----------------
108 | Westward 1 | Woody Allen
111 | Walt Disney 2 | Warren Beatty
112 | Warner Bros. 3 | Walter Matthau
... ...
SELECT distributors.name
FROM distributors
WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
FROM actors
WHERE actors.name LIKE 'W%';
----------------
Walt Disney
Walter Matthau
Warner Bros.
Warren Beatty
Westward
Woody Allen
这个例子显示了如何在FROM子句中使用函数,包括带有和不带字段定义列表的。
CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;
SELECT * FROM distributors(111);
did | name
-----+-------------
111 | Walt Disney
CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;
SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
f1 | f2
-----+-------------
111 | Walt Disney
这个例子显示了如何使用一个简单的WITH子句:
WITH t AS (
SELECT random() as x FROM generate_series(1, 3)
SELECT * FROM t
UNION ALL
SELECT * FROM t
--------------------
0.534150459803641
0.520092216785997
0.0735620250925422
0.534150459803641
0.520092216785997
0.0735620250925422
请注意WITH查询只评估一次,所以我们获得两组相同的三个随机值。
这个例子使用WITH RECURSIVE
从一个只显示直接属下的表中找到所有职员Mary的属下(直接或间接),和他们的间接级别,
WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
SELECT 1, employee_name, manager_name
FROM employee
WHERE manager_name = 'Mary'
UNION ALL
SELECT er.distance + 1, e.employee_name, e.manager_name
FROM employee_recursive er, employee e
WHERE er.employee_name = e.manager_name
SELECT distance, employee_name FROM employee_recursive;