SQL: Queries: Table Expressions

20th August 2020 at 2:19pm
SQL: Queries

Table expression 包含几个子句:FROMWHEREGROUP BYHAVING。DB 在处理查询语句的顺序是:

  1. FROM 指示的表中拿出数据
  2. 如果有 JOIN,按 JOIN 的条件组合数据
  3. WHERE 筛选数据
  4. GROUP BY 聚合数据
  5. HAVING 筛选聚合后的数据

The FROM Clause

语法:

FROM table_reference [, table_reference [, ...]]

Table reference 可以是:

  • 一个表名
  • 派生的表,比如子查询所产生的表
  • JOIN 结构
  • 上述几项的组合

多个 table reference 一起出现在 FROM 子句中时,它们是 cross-joined(交叉相联)的。Join 后的结果作为一个虚拟表继续被处理。

Joined Tables 重要

See SQL: Queries: Table Expressions: Join.

Table And Column Aliases 重要

See SQL: Queries: Table Expressions: Alias.

Subqueries 重要

子查询在 SQL 语句中被括号包起,并且需要指定一个表别名。它的查询结果作为一张虚拟表继续被处理。例如:

FROM (SELECT * FROM table1) AS <alias_name>

子查询也可以是一个 VALUES 列表:

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
     AS names(first, last)

Table Functions 不重要

少看到有人使用,略。

LATERAL Subqueries 不重要

少看到有人使用,略。

The WHERE Clause 重要

WHERE 相对简单,它接受一个返回 boolean 值的表达式。常见示例:

SELECT ... FROM fdt WHERE c1 > 5
SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)
SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

The GROUP BY and HAVING Clauses 重要

GROUP BY 会把某列里面同样的值聚合成一行返回:

=> SELECT * FROM test1;
 x | y
---+---
 a | 3
 c | 2
 b | 5
 a | 1
(4 rows)

=> SELECT x FROM test1 GROUP BY x;
 x
---
 a
 b
 c
(3 rows)

它经常跟聚合函数(aggregate function)一起配合使用,比如 sum()

=> SELECT x, sum(y) FROM test1 GROUP BY x;
 x | sum
---+-----
 a |   4
 b |   5
 c |   2
(3 rows)

=> SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
    FROM products p LEFT JOIN sales s USING (product_id)
    GROUP BY product_id, p.name, p.price;

在 PG 中,GROUP BY 后面的列可以是 SELECT 中的列,也可以是某个 value expression(没有示例)。

HAVING 子句则是在 GROUP BY 之后做一轮筛选:

SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
    FROM products p LEFT JOIN sales s USING (product_id)
    WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
    GROUP BY product_id, p.name, p.price, p.cost
    HAVING sum(p.price * s.units) > 5000;

HAVING 中的条件,可以使用聚合函数也可以不使用(WHERE 中的条件则无法使用聚合函数);这些条件也不必跟 SELECT 列表中的某项一致,比如上面例子中的 sum(p.price * s.units) 并没有出现在 SELECT 列表中。

GROUPING SETS, CUBE, and ROLLUP 不重要

略。

Window Function Processing 不重要

略。

TOC