初探 SQL 高级语法 - CTE （公共表表达式 ）

初探 SQL 高级语法 - CTE （公共表表达式 ）

CTE (Common Table Expression) 公共表达式是一个可以复用的临时结果集，可以用于重构带有很多子查询的复杂 SQL 语句。

与临时表不同的是，它的结果集只在当前的语句中有效，不会建立实际的表；CTE 的结果集可以多次使用，更容易组织 SQL 语句。

以下是各 DBMS 对 WITH 语句的支持情况：

  • Oracle: WITH 语法最早出现在 Oracle 9i 版本中
  • MySQL: WITH 语法在 MySQL 8.0 版本中被引入
  • MariaDB: WITH 语法在 MariaDB 10.2 版本中被引入
  • PostgreSQL: WITH 语法最早出现在 PostgreSQL 8.4 版本中

基本用法

一般通过 WITH 语句创建公共表表达式，基本语法为：

WITH [RECURSIVE] 
  cte_name [(col_name [, col_name] ...)] as (subquery)
  [, cte_name [(col_name [, col_name] ...)] as (subquery)] ...

WITH 语句块后面可以跟 SELECT, UPDATE, DELETE 语句，后面的语句可以 cte_name 为表名做 SQL 操作。

WITH 拥有递归查询功能，它可以引用已经运行的查询结果做下一次查询，非常适合解决树形输出等需求。

使用案例

简单查询


create table test_lock (
    id bigint not null,
    name varchar(32) not null,
    primary key(id)
);

create table the_user (
    id bigint not null,
    lock_id bigint not null,
    NAME VARchar(32),
    address varchar(32),
    primary key (id)
);
-- 利用 CTE 临时结果做查询
with c1 as (
	select u.id, lock_id, l.name lock_name, u.name, address 
	  from the_user u 
	  join test_lock l on u.lock_id = l.id
)
select id, lock_id, lock_name, name from c1;

-- 直接建表
create table my_table
with c1 as (
	select u.id, lock_id, l.name lock_name, u.name, address 
	  from the_user u 
	  join test_lock l on u.lock_id = l.id
)
select id, lock_id, lock_name, name from c1;

递归查询

create table test (
  id bigint not null primary key,
  parent_id bigint,
  name varchar(32)
);


insert into test (id, parent_id, name)
values
(1, null, '浙江省'),
(2, 1, '杭州市'),
(3, 2, '西湖区'),
(4, 2, '下城区'),
(5, 3, '转唐街道'),
(6, 1, '宁波市'),
(7, 6, '海曙区')
;

with recursive tree as (
    select id, name, name as full_path from test where id = 1
    union ALL
    select test.id, test.name, concat(tree.full_path, '->', test.name) as full_path from test, tree where tree.id = test.parent_id
) select id, name, full_path from tree;

会输出这样的数据：

id name full_path
1 浙江省 浙江省
2 杭州市 浙江省->杭州市
6 宁波市 浙江省->宁波市
3 西湖区 浙江省->杭州市->西湖区
4 下城区 浙江省->杭州市->下城区
7 海曙区 浙江省->宁波市->海曙区
5 转唐街道 浙江省->杭州市->西湖区->转唐街道

对子查询的重构

比如以下例子，要查询每个客户最后购买的产品，使用子查询与 CTE 的写法对比如下：

CREATE TABLE customer (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100)
);

CREATE TABLE product (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100)
);

CREATE TABLE the_order (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  create_datetime timestamp default current_timestamp
);

insert into customer (customer_id, customer_name) values 
 (1, 'name1')
,(2, 'name2')
,(3, 'name3')
;
insert into product (product_id, product_name) values 
 (1, 'pname1')
,(2, 'pname2')
,(3, 'pname3')
;
insert into the_order (order_id, customer_id, product_id, create_datetime) values 
 (1, 1, 1, '2023-01-01 12:00:00')
,(2, 1, 1, '2023-02-01 12:00:00')
,(3, 1, 3, '2023-03-01 12:00:00')
,(4, 2, 1, '2023-02-01 12:00:00')
,(5, 3, 3, '2023-02-01 12:00:00')
,(6, 3, 1, '2023-03-01 12:00:00')
;
-- 使用子查询
select o.order_id, o.customer_id, c.customer_name, p.product_name, o.create_datetime
from customer c
join the_order o on c.customer_id = o.customer_id
join product p on o.product_id = p.product_id
where (o.customer_id, o.create_datetime) in (
    select customer_id, max(create_datetime) from the_order group by customer_id
);

-- 使用 CTE
with tmp as (
  select customer_id, max(create_datetime) dt from the_order group by customer_id
)
select o.order_id, o.customer_id, c.customer_name, p.product_name, o.create_datetime
from customer c
join the_order o on c.customer_id = o.customer_id
join product p on o.product_id = p.product_id
join tmp on o.customer_id = tmp.customer_id and o.create_datetime = tmp.dt

总结

由于 CTE 可重复使用的特性，在处理特定场景的复杂 SQL 时可以考虑使用，来提高 SQL 的性能与可读性。

参考资料

posted on 2023-05-06 14:04  滞人  阅读(1436)  评论(0)    收藏  举报