ANSI SQL 及实现调研 (一)

背景

经常看到 SQL-92，SQL-03 的词汇，但是不了解其中的差异，比如 SQL-03 比 SQL-92 多了些什么?
知道不同的数据库有不同的方言，但是不了解哪些是 SQL 标准中定义的大家都遵守的。
开发一个类 SQL 语言时，选择支持哪些语法好呢？

本文从 ANSI SQL 切入，来调研一下 ANSI SQL 标准(下面简称为 SQL 标准)的演变，以及不同数据库对 SQL 标准的支持程度。

历程

通过 SQL Wikipedia 上了解到：

由于不同的数据库产商的 SQL 语法不兼容，因此 1986 年开始，ANSI 和 ISO 标准化组织采纳了 SQL 语言标准。并且在随后的年份陆续公布了新的标准。

具体年份和名称如下：

年份	名称	备注
1986	SQL-86	第一版
1989	SQL-89	小修改
1992	SQL-92	大修改
1999	SQL:1999	加入嵌套表(nested table[4]), 加入正则表达式, 递归查询，触发器，过程和控制流，数组类型和一些结构化类型
2003	SQL:2003	加入 XML 相关特征，窗口函数，标准化序列(standardized sequences)，自动生成值的列
2006	SQL:2006	支持导入，存储和操作 XML 数据，加入 XQuery
2008	SQL:2008	支持 cursor 定义外的 ORDER BY?, 增加 INSTEAD OF 触发器，增加 TRUNCATE，增加 FETCH
2011	SQL:2011	增加 temporal data，增强 window functions 和 FETCH
2016	SQL:2016	增加列特征匹配(row pattern matching), 多态表函数(polymorphic table functions), JSON

标准公开出来后，不同的产商的实现情况也是不兼容的，即不完全遵守标准。主要是日期类型，时间类型，字符串拼接，NULL 和大小写敏感。

只有 PostgreSQL 和 Mimer SQL 是紧跟标准的。

变化

请找一个充足的时间，打开 Modern SQL: Slides，从头到尾看一遍。

或者打开 Modern SQL in Open Source and Commercial Databases，下载下来看。

这里做一下读书笔记

LATERAL

来自 SQL:1999

依旧拿 PostgreSQL 初体验中的示例数据举例，现在有两张表 cities 和 weather 如下：


mydb=# select * from cities;
     name      | location
---------------+-----------
 San Francisco | (-194,53)
(1 row)


mydb=# select * from weather;
     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      43 |      57 |    0 | 1994-11-29
 Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)

JOIN LATERAL

现在两张表要做一个 JOIN

在 SQL:1999 之前，只能这么写：


SELECT *
FROM cities
	JOIN (
		SELECT *
		FROM weather
	) inline_view
	ON inline_view.city = cities.name

执行结果


     name      | location  |     city      | temp_lo | temp_hi | prcp |    date
---------------+-----------+---------------+---------+---------+------+------------
 San Francisco | (-194,53) | San Francisco |      43 |      57 |    0 | 1994-11-29
 San Francisco | (-194,53) | San Francisco |      46 |      50 | 0.25 | 1994-11-27
(2 rows)

在 SQL:1999 之后，可以这样写：


SELECT *
FROM cities
	JOIN lateral(
		SELECT *
		FROM weather
		WHERE weather.city = cities.name
	) inline_view ON true

结果


     name      | location  |     city      | temp_lo | temp_hi | prcp |    date
---------------+-----------+---------------+---------+---------+------+------------
 San Francisco | (-194,53) | San Francisco |      43 |      57 |    0 | 1994-11-29
 San Francisco | (-194,53) | San Francisco |      46 |      50 | 0.25 | 1994-11-27
(2 rows)

注意两点：

在 inline_view 中，引用了外部的 cities
JOIN 使用了 join lateral 关键字，而不仅仅是 join

好奇一下内部的执行过程是不是一样的，使用 explain 来看一下


EXPLAIN SELECT *
FROM cities
	JOIN (
		SELECT *
		FROM weather
	) inline_view
	ON inline_view.city = cities.name;

结果


                               QUERY PLAN
------------------------------------------------------------------------
 Hash Join  (cost=18.10..55.28 rows=648 width=388)
   Hash Cond: ((cities.name)::text = (weather.city)::text)
   ->  Seq Scan on cities  (cost=0.00..13.60 rows=360 width=194)
   ->  Hash  (cost=13.60..13.60 rows=360 width=194)
         ->  Seq Scan on weather  (cost=0.00..13.60 rows=360 width=194)
(5 rows)


EXPLAIN SELECT *
FROM cities
	JOIN lateral(
		SELECT *
		FROM weather
		WHERE weather.city = cities.name
	) inline_view ON true;

结果


                               QUERY PLAN
------------------------------------------------------------------------
 Hash Join  (cost=18.10..55.28 rows=648 width=388)
   Hash Cond: ((cities.name)::text = (weather.city)::text)
   ->  Seq Scan on cities  (cost=0.00..13.60 rows=360 width=194)
   ->  Hash  (cost=13.60..13.60 rows=360 width=194)
         ->  Seq Scan on weather  (cost=0.00..13.60 rows=360 width=194)
(5 rows)

这两个的实际执行过程是一样的。

CROSS JOIN LATERAL

使用 cross join lateral 也可以实际同样的效果，同时可以不加末尾的 on true


SELECT *
FROM cities
	CROSS JOIN lateral(
		SELECT *
		FROM weather
		WHERE weather.city = cities.name
	) inline_view;

结果


     name      | location  |     city      | temp_lo | temp_hi | prcp |    date
---------------+-----------+---------------+---------+---------+------+------------
 San Francisco | (-194,53) | San Francisco |      43 |      57 |    0 | 1994-11-29
 San Francisco | (-194,53) | San Francisco |      46 |      50 | 0.25 | 1994-11-27
(2 rows)

内部执行过程仍然是相同的


EXPLAIN SELECT *
FROM cities
	CROSS JOIN lateral(
		SELECT *
		FROM weather
		WHERE weather.city = cities.name
	) inline_view;

结果


                               QUERY PLAN
------------------------------------------------------------------------
 Hash Join  (cost=18.10..55.28 rows=648 width=388)
   Hash Cond: ((cities.name)::text = (weather.city)::text)
   ->  Seq Scan on cities  (cost=0.00..13.60 rows=360 width=194)
   ->  Hash  (cost=13.60..13.60 rows=360 width=194)
         ->  Seq Scan on weather  (cost=0.00..13.60 rows=360 width=194)
(5 rows)

意义

这种写法有什么意义呢？比原来的写法的优越性在哪里呢？

一方面是 LATERAL 和 table functions 可以联合使用。这个目前是 PostgreSQL 中特有的，本文略过。

LATERAL & LIMIT

另一方面是可以在 inline view 中加入 LIMIT 操作，比如：


SELECT *
FROM cities
	CROSS JOIN lateral(
		SELECT *
		FROM weather
		WHERE weather.city = cities.name
		ORDER BY temp_lo
		LIMIT 1
	) inline_view;

结果


     name      | location  |     city      | temp_lo | temp_hi | prcp |    date
---------------+-----------+---------------+---------+---------+------+------------
 San Francisco | (-194,53) | San Francisco |      43 |      57 |    0 | 1994-11-29
(1 row)

如果不使用 LATERAL 的话，是无法实现只取某一列的前 LIMIT 行的。

LATERAL & Multi-Source Top-N

场景：用户找出每个分类下最新的 10 条新闻。


SELECT n.*
FROM news n
	JOIN subscriptions s 
	ON n.topic = s.topic
WHERE s.user = ?
ORDER BY n.created DESC
LIMIT 10

这样会把每个 subscriptions 下的所有的 news 都找出来，但是是没有必要的。

使用 LATERAL 的写法如下：


SELECT n.*
FROM subscriptions s
	JOIN LATERAL(
		SELECT *
		FROM news n
		WHERE n.topic = s.topic
		ORDER BY n.created DESC
		LIMIT 10
	) top_news ON true
WHERE s.user_id = ?
ORDER BY n.created DESC
LIMIT 10;

这样可以做到只取每个 news 下的前 10 条新闻.

小结

SQL 中的"FOR EACH"
适合与 OUTER JOINS 联用
适合取子查询中的 Top-N
可以与 table functions 作 join

支持的数据库

DB2 LUW 9.1+
Oracle 12c+
PostgreSQL 9.3+
SQL Server 2005 部分支持
MySQL 不支持
SQLite 不支持

WITH (Common Table Expressions)

来自 SQL:1999

WITH 是解决什么问题的呢？

背景

OK，先来看一个在 SQL:1999 之前的场景：

当遇到多层嵌套的 SQL 时，我们会这样写。


SELECT ...
  FROM (SELECT ...
          FROM t1
		  JOIN (SELECT ... FROM ...
		       ) a ON (...)
	   ) b
  JOIN (SELECT ... FROM ...
       ) c ON (...)

下面的 SQL 是为了示例而示例：


SELECT *
FROM (
	SELECT *
	FROM cities
) a
	JOIN (
		SELECT *
		FROM weather
	) b
	ON a.name = b.city;

结果


     name      | location  |     city      | temp_lo | temp_hi | prcp |    date
---------------+-----------+---------------+---------+---------+------+------------
 San Francisco | (-194,53) | San Francisco |      43 |      57 |    0 | 1994-11-29
 San Francisco | (-194,53) | San Francisco |      46 |      50 | 0.25 | 1994-11-27
(2 rows)

当去理解这样的 SQL 语句时，需要先理解最里面的 SELECT，再理解次里面的 SELECT，一层一层的向外看，最后再理解最外层的 SELECT。在 SQL92 时，就是这样来做的。

如果有了 WITH CTEs，就可以用更易理解的方式来写 SQL 了。

语法

一个 CTE


WITH
  a (c1, c2, c3)
AS (SELECT c1, c2, c3 FROM ...)
SELECT ...

两个 CTEs


WITH
  a (c1, c2, c3)
AS (SELECT c1, c2, c3 FROM ...), 
  b (c4, ...)
AS (SELECT c4, ... 
    FROM t1
	JOIN a
	  ON(...)
	)

SELECT ...

多个 CTE 之间由逗号连接，只有第一个 CTE 前有 WITH 关键字，后面的 CTE 不需要 WITH 关键字。
在第二个 CTE 中可以引用第一个 CTE，即在后面的 CTE 可以引用前面的 CTE。
最后一个 CTE 后没有逗号，表明后面跟着的是查询语句。

这样的话，看 SQL 时就可以从上往下来看了。

将上面的示例重写一下：


WITH a (name, location) AS (
		SELECT name, location
		FROM cities
	), 
	b (city, temp_lo, temp_hi, prcp, date) AS (
		SELECT city, temp_lo, temp_hi, prcp, date
		FROM weather
	)
SELECT *
FROM a
	JOIN b ON a.name = b.city;

结果如下：


     name      | location  |     city      | temp_lo | temp_hi | prcp |    date
---------------+-----------+---------------+---------+---------+------+------------
 San Francisco | (-194,53) | San Francisco |      43 |      57 |    0 | 1994-11-29
 San Francisco | (-194,53) | San Francisco |      46 |      50 | 0.25 | 1994-11-27
(2 rows)

小结

WITH 是 SQL 中的"私有方法 private methods"
WITH 视图可以多次被引用
WITH 通过定义多个 CTE，来实现嵌套
可以用 SELECT 的地方，就可以用 WITH，如 INSERT INTO tbl WITH ... SELECT

坑

下推(push down)

需要注意的是，在 PostgreSQL 中，WITH 视图更像是一个物化视图，没有把查询中的过滤条件下推到视图中。

在数据量大时，可能会有性能问题。使用时请用 EXPLAIN 来仔细检查一下。

但是在 PostgreSQL 中使用 inline 视图时，会有下推的操作的。

上推(push up)

PostgreSQL 9.1+ 中，INSERT, UPDATE 和 DELETE 中也支持 WITH。

如将指定行从一个表移动到另一个表


WITH deleted_rows AS(
  DELETE FROM source_tbl
  RETURNING *
)
INSERT INTO destination_tbl
SELECT * FROM deleted_rows

支持的数据库

DB2 LUW 7+
Oracle 9iR2+
PostgreSQL 8.4+
SQL Server 2005+
SQLite 3.8.3+
MySQL 8.0+

WITH RECURSIVE (Common Table Expressions)

来自 SQL:1999

从一个例子来看起。

生成 1 到 3 的数字


WITH RECURSIVE cte (n)
  AS (SELECT 1
	   UNION ALL
	  SELECT n+1
	    FROM cte
	  WHERE n < 3)
SELECT * FROM cte;

结果


 n
---
 1
 2
 3
(3 rows)

这个是怎么实现的呢？

首先其中的 SELECT 中有两项，由 UNION (ALL)分开: 初始项和递归项，见 SELECT 初始项 UNION ALL 递归项

初始时，由初始项产生临时表
随时，临时表做为递归项的输入表，再生产新的临时表
直到新的临时表为空时结束

这样，就形成了一个循环。

来看更多的例子

生成 2 到 4 的数字


WITH RECURSIVE cte (n)
  AS (SELECT 2
	   UNION ALL
	  SELECT n + 1
	    FROM cte
	  WHERE n < 4)
SELECT * FROM cte;

结果


 n
---
 2
 3
 4
(3 rows)

生成 1,3,5 的数字


WITH RECURSIVE cte (n)
  AS (SELECT 1
	   UNION ALL
	  SELECT n + 2
	    FROM cte
	  WHERE n < 5)
SELECT * FROM cte;

结果


 n
---
 1
 3
 5
(3 rows)

阶乘

再看一个求阶乘的例子


WITH RECURSIVE factorial(F,n) AS (
  SELECT 1 F, 4 n
UNION ALL
  SELECT F*n F, n-1 n
  FROM factorial
  WHERE n > 1)
SELECT * FROM factorial;

结果


 f  | n
----+---
  1 | 4
  4 | 3
 12 | 2
 24 | 1
(4 rows)

即 4 的阶乘为 24。

求树的子节点

构造一颗树，每一个节点有自己编号 id, 和其父节点的编号 parent_id

建表


CREATE TABLE t (
  id NUMERIC NOT NULL,
  parent_id NUMERIC,
  PRIMARY KEY (id)
)

构造数据


INSERT INTO t VALUES (1, null);

INSERT INTO t VALUES (2, 1);
INSERT INTO t VALUES (3, 1);
INSERT INTO t VALUES (4, 1);
INSERT INTO t VALUES (5, 1);

INSERT INTO t VALUES (6, 2);
INSERT INTO t VALUES (7, 3);
INSERT INTO t VALUES (8, 3);
INSERT INTO t VALUES (9, 4);
INSERT INTO t VALUES (10, 5);

INSERT INTO t VALUES (11, 6);
INSERT INTO t VALUES (12, 8);
INSERT INTO t VALUES (13, 8);
INSERT INTO t VALUES (14, 8);
INSERT INTO t VALUES (15, 8);
INSERT INTO t VALUES (16, 10);

查看一下表中的数据


mydb=# select * from t;
 id | parent_id
----+-----------
  1 |
  2 |         1
  3 |         1
  4 |         1
  5 |         1
  6 |         2
  7 |         3
  8 |         3
  9 |         4
 10 |         5
 11 |         6
 12 |         8
 13 |         8
 14 |         8
 15 |         8
 16 |        10
(16 rows)

现在来找到节点 3 其及所有子节点。

传统写法

可以使用多个 LEFT JOIN 来写成，同时自己的结果再进行二次处理。


SELECT d0.*, d1.*, d2.* 
  FROM t AS d0
  LEFT JOIN t AS d1
    ON (d1.parent_id = d0.id)
  LEFT JOIN t AS d2
    ON (d2.parent_id = d1.id)
  WHERE d0.id = 3

结果如下：


 id | parent_id | id | parent_id | id | parent_id
----+-----------+----+-----------+----+-----------
  3 |         1 |  8 |         3 | 12 |         8
  3 |         1 |  8 |         3 | 13 |         8
  3 |         1 |  8 |         3 | 14 |         8
  3 |         1 |  8 |         3 | 15 |         8
  3 |         1 |  7 |         3 |    |
(5 rows)

WITH RECURSIVE CTEs 写法


WITH RECURSIVE subtree (id, parent_id) AS
(
		SELECT id, parent_id 
		FROM t
		WHERE id = 3
	UNION ALL
		SELECT t.id, t.parent_id
		FROM subtree
		LEFT JOIN t
			ON (t.parent_id = subtree.id)
	    WHERE t.id <= 20
)
SELECT * FROM subtree;

结果


 id | parent_id
----+-----------
  3 |         1
  7 |         3
  8 |         3
 12 |         8
 13 |         8
 14 |         8
 15 |         8
(7 rows)

小结

SQL 中的 while 循环
列生成器
图处理器
可以将数据传递到下一个迭代中
需要有终止条件，否则容易死循环

支持的数据库

DB2 LUV 7+
Oracle 11.gR2+
PostgreSQL 8.4+
SQL Server 2005+
SQLite 3.8.3+
MySQL 不支持

OVER/PARTITION BY

来自 SQL:2003

先从一个场景来说起，如何实现部分收入百分比呢？

构造数据


CREATE TABLE empsalary(
  depname varchar,
  empno bigint,
  salary int,
  enroll_date date
);

INSERT INTO empsalary VALUES('develop',10, 5200, '2007/08/01');
INSERT INTO empsalary VALUES('sales', 1, 5000, '2006/10/01');
INSERT INTO empsalary VALUES('personnel', 5, 3500, '2007/12/10');
INSERT INTO empsalary VALUES('sales', 4, 4800, '2007/08/08');
INSERT INTO empsalary VALUES('sales', 6, 5500, '2007/01/02');
INSERT INTO empsalary VALUES('personnel', 2, 3900, '2006/12/23');
INSERT INTO empsalary VALUES('develop', 7, 4200, '2008/01/01');
INSERT INTO empsalary VALUES('develop', 9, 4500, '2008/01/01');
INSERT INTO empsalary VALUES('sales', 3, 4800, '2007/08/01');
INSERT INTO empsalary VALUES('develop', 8, 6000, '2006/10/01');
INSERT INTO empsalary VALUES('develop', 11, 5200, '2007/08/15');

SQL:2003 之前解法

在 SQL:2003 之前


WITH total_salary_by_department
  AS (SELECT depname, SUM(salary) total 
      FROM empsalary
	  GROUP BY depname)
SELECT ts.depname, empno, salary, 
  100 * salary/ts.total  "% of dep"
  FROM empsalary
  JOIN total_salary_by_department ts
    ON (empsalary.depname = ts.depname)
  ORDER BY depname, "% of dep" desc;

结果


 develop   |     8 |   6000 |       23
 develop   |    10 |   5200 |       20
 develop   |    11 |   5200 |       20
 develop   |     9 |   4500 |       17
 develop   |     7 |   4200 |       16
 personnel |     2 |   3900 |       52
 personnel |     5 |   3500 |       47
 sales     |     6 |   5500 |       27
 sales     |     1 |   5000 |       24
 sales     |     3 |   4800 |       23
 sales     |     4 |   4800 |       23

如果只看某一个部门的呢？


WITH total_salary_by_department
  AS (SELECT depname, SUM(salary) total 
      FROM empsalary
	  GROUP BY depname)
SELECT ts.depname, empno, salary, 
  100 * salary/ts.total  "% of dep"
  FROM empsalary
  JOIN total_salary_by_department ts
    ON (empsalary.depname = ts.depname)
  WHERE empsalary.depname = 'develop'
  ORDER BY depname, "% of dep" desc;

结果


 develop |     8 |   6000 |       23
 develop |    10 |   5200 |       20
 develop |    11 |   5200 |       20
 develop |     9 |   4500 |       17
 develop |     7 |   4200 |       16

好吧，这个又是上面提到的 PostgreSQL 的性能问题。

在 SQL:2003 以前，想实现 Aggregation 的话，只有通过 DISTINCT 和 GROUP BY 这两种方法。如果不想选项被合并的话，那就只有 GROUP BY 一种方法。

SQL:2003

在 SQL:2003 中，引入了 OVER(PARTITION BY)语法，来解法此类问题。

如上面的 SQL，可以写为：


SELECT depname,
         empno,
         salary,
         100 * salary /  SUM(salary)
	            OVER(PARTITION BY depname) "% of depname"
	   FROM empsalary
	   ORDER BY 
	     depname, 
	     "% of depname" DESC;

结果


 develop   |     8 |   6000 |           23
 develop   |    10 |   5200 |           20
 develop   |    11 |   5200 |           20
 develop   |     9 |   4500 |           17
 develop   |     7 |   4200 |           16
 personnel |     2 |   3900 |           52
 personnel |     5 |   3500 |           47
 sales     |     6 |   5500 |           27
 sales     |     1 |   5000 |           24
 sales     |     3 |   4800 |           23
 sales     |     4 |   4800 |           23

这里先选择了 salary 列，然后选择对 salary 列做 SUM，同时通过 PARTIITON BY 关键字指定了 SUM 的窗口是针对 dep 列。

小结

OVER 可以与任何聚合函数搭配作用
略
略
OVER (PARTITION BY X)可以实现类似 GROUP BY 的效果

OVER/ORDER BY

收支场景：

有一张表 transactions 记录着自己的收支情况。现在需要显示收支的明细和对应的余额。

构造数据


CREATE TABLE transactions(
  txid int,
  value numeric
);

INSERT INTO transactions VALUES(1, +10);
INSERT INTO transactions VALUES(2, +20);
INSERT INTO transactions VALUES(3, -10);
INSERT INTO transactions VALUES(4, +50);
INSERT INTO transactions VALUES(5, -30);
INSERT INTO transactions VALUES(6, -20);

查看如下


mydb=# select * from transactions;
  1 |    10
  2 |    20
  3 |   -10
  4 |    50
  5 |   -30
  6 |   -20

SQL:2003 之前


SELECT txid, 
       value,
       (SELECT SUM(value)
	      FROM transactions tx2
	   WHERE tx2.txid <= tx1.txid) balance
    FROM transactions tx1
    ORDER BY txid;

结果


    1 |    10 |      10
    2 |    20 |      30
    3 |   -10 |      20
    4 |    50 |      70
    5 |   -30 |      40
    6 |   -20 |      20

SQL:2003


SELECT txid, 
       value,
	   SUM(value)
	   OVER(ORDER BY txid
	       ROWS
		   BETWEEN UNBOUNDED PRECEDING
		       AND CURRENT ROW
		   ) balance
    FROM transactions tx1
    ORDER BY txid;

结果


    1 |    10 |      10
    2 |    20 |      30
    3 |   -10 |      20
    4 |    50 |      70
    5 |   -30 |      40
    6 |   -20 |      20

新的方法

ROW_NUMBER

排序方法

RANK
DENSE_RANK
PERCENT_RANK
CUME_DIST

支持的数据库

DB2 LUW: 7+
Oracle: 8i+
PostgreSQL: 8.4+
SQL Server: 2005+
MySQL: 不支持
SQLite: 不支持

OVER/LAG

SQL:2008

场景：显示与前一项的差值。和上面的场景正好相反。

SQL:2008 前

显示 ROW_NUMBER


 SELECT *, ROW_NUMBER() OVER (ORDER BY txid) rn FROM transactions;
    1 |    10 |  1
    2 |    20 |  2
    3 |   -10 |  3
    4 |    50 |  4
    5 |   -30 |  5
    6 |   -20 |  6

然后使用上面提到的 SQL:2003 的 WITH 方法，来求差值。


WITH numbered_data AS (
  SELECT *, 
      ROW_NUMBER() OVER (ORDER BY txid) rn 
	  FROM transactions
)
SELECT cur.txid, cur.value, cur.value - prev.value
  FROM      numbered_data cur
  LEFT JOIN numbered_data prev
    ON (cur.rn - 1 = prev.rn);

结果


    1 |    10 |
    2 |    20 |       10
    3 |   -10 |      -30
    4 |    50 |       60
    5 |   -30 |      -80
    6 |   -20 |       10

SQL:2008


SELECT *, 
       value - LAG(value)
	           OVER (ORDER BY txid)
	  FROM transactions;

结果：


    1 |    10 |
    2 |    20 |       10
    3 |   -10 |      -30
    4 |    50 |       60
    5 |   -30 |      -80
    6 |   -20 |       10

其它

同样的还有：


  LEAD / LAG
  FIRST_VALUE / LAST_VALUE
  MIN_VALUE(col, n) FROM FIRST/LAST
                         RESPECT/IGNORE NULLS

支持的数据库

DB2 LUW 9.5+
Oracle: 11gR2+
PostgreSQL 8.4+
SQL Server 2012+
MySQL: 不支持

FETCH FIRST

SQL:2008

场景：找到选定行的前有限列

SQL:2008 之前


SELECT *
  FROM (SELECT *,
          ROW_NUMBER() OVER (ORDER BY txid) rn
        FROM transactions) numbered_data
  WHERE rn <= 3;

结果


    1 |    10 |  1
    2 |    20 |  2
    3 |   -10 |  3

当然也可以使用非标准的语法，如 LIMIT, TOP。

SQL:2008 之后


SELECT *
    FROM transactions
  ORDER BY txid
  FETCH FIRST 3 ROWS ONLY;

结果


    1 |    10
    2 |    20
    3 |   -10

支持的数据库

DB2 LUW 7+
MySQL 3.19.3+
Oracle 12c
PostgreSQL 8.4+
SQL Server 2012+
SQLite 2.1.0+

OFFSET

SQL: 2011

这个很常见了，但是只是在 SQL:2011 中才引入。

支持的数据库

DB2 LUW 9.7+
MySQL 4.0.6+
Oracle 12c+
PostgreSQL 6.5+
SQL Server 2012+
SQLite 2.1.0+

AS OF

场景：记录数据记录的产生时间

SQL:2011 中


CREATE TABLE t( ...,
    start_ts TIMESTAMP(9) GENERATED
	         ALWAYS AS ROW START,
	end_ts   TIMESTAMP(9) GENERATED
	         ALWAYS AS ROW END,
	PERIOD FOR SYSTEM TIME (start_ts, end_ts)
) WITH SYSTEM VERSIONING

写入


INSERT ... (ID, DATA) VALUES (1, 'X')

SQL:2011

其中的 start_ts 和 end_ts 会自动修改，对应用透明。

支持的数据库

DB2 LUW 10.1+
Oracle 10gR1+
MySQL 不支持
PostgreSQL 不支持
SQL Server 不支持
SQLite 不支持

WITHOUT OVERLAPS

场景：每行日志有起止时间，要求约束起止时间不重叠。

SQL:2011

SQL:2011 中引入了 temporal 类型和 bi-temporal 类型，如：


PRIMARY KEY (id, period WITHOUT OVERLAPS)

支持的数据库

DB2 LUW 10.1+
PostgreSQL 9.2+
MySQL 不支持
Oracle 不支持
SQL Server 不支持
SQLite 不支持

ANSI SQL 及实现调研 (一)

背景

历程

变化

LATERAL

JOIN LATERAL

CROSS JOIN LATERAL

意义

LATERAL & LIMIT

LATERAL & Multi-Source Top-N

小结

支持的数据库

WITH (Common Table Expressions)

背景

语法

小结

坑

下推(push down)

上推(push up)

支持的数据库

WITH RECURSIVE (Common Table Expressions)

生成 1 到 3 的数字

生成 2 到 4 的数字

生成 1,3,5 的数字

阶乘

求树的子节点

传统写法

WITH RECURSIVE CTEs 写法

小结

支持的数据库

OVER/PARTITION BY

构造数据

SQL:2003 之前解法

SQL:2003

小结

OVER/ORDER BY

构造数据

SQL:2003 之前

SQL:2003

新的方法

排序方法

支持的数据库

OVER/LAG

SQL:2008 前

SQL:2008

其它

支持的数据库

FETCH FIRST

SQL:2008 之前

SQL:2008 之后

支持的数据库

OFFSET

支持的数据库

AS OF

SQL:2011 中

SQL:2011

支持的数据库

WITHOUT OVERLAPS

SQL:2011

支持的数据库

参考

相关帖子

Apache Kudu 初体验

思源笔记 v3.1.31 模板使用问题

QNAP 的 webdav 同步总是在最后失败

关机后打开发现笔记全部丢失

请问群晖 Doker compose 如何部署 siyuan 笔记

docker 版思源笔记开启发布服务的 basic 认证就无法访问也不弹登录框，关闭就可以访问

链滴个人主页不登录时也能被访问和搜索增加第三方搜索引擎

欢迎来到这里！