SQL Server CTE (Common Table Expression) 公用表表达式
2023-09-27 14:23:55 时间
参考:
特色
1. CTE 可以引用自身, 实现递归查询. (Oracle 用 connect by prior)
2. 它有点像表变量, 其后的 query 都可以引用它. 然后自动销毁. 很方便
3. 可读性很棒
复用与可读性优化
来看一个例子, 有一个 product 表
go create table product ( id int identity primary key, name nvarchar(max) not null default '', [group] nvarchar(max) not null default '', price decimal not null default 0 ); insert into product (name, [group], price) values ('product 1', 'group 1', 100), ('product 2', 'group 1', 100), ('product 3', 'group 1', 100), ('product 4', 'group 2', 100), ('product 5', 'group 2', 150), ('product 6', 'group 2', 100), ('product 7', 'group 3', 100), ('product 8', 'group 3', 50), ('product 9', 'group 3', 100); go
需求是
1. 把 product group by [group], 并且计算出 sum price as total_price
2. 计算出 ave total_price
3. filter 出 total_price >= ave_total_price 的 group
Without CTE
首先是 group by [group] 语句
select [group], sum(price) as total_price from product group by [group];
然后是 ave 语句
select cast(avg(total.total_price) as int) as avg_price from ( select [group], sum(price) as total_price from product group by [group] ) total;
注意, 中间的语句是重复的 (管理扣分)
最后是
select [group], total_price from (select [group], sum(price) as total_price from product group by [group]) pg inner join (select cast(avg(total.total_price) as int) as avg_price from ( select [group], sum(price) as total_price from product group by [group] ) total ) pap on pg.total_price >= pap.avg_price;
虽然结果是正确的, 但是语句重复很多, 可读性太差了.
With CTE
with product_group as ( select [group], sum(price) as total_price from product group by [group] ), product_avg_price as ( select cast(avg(total_price) as int) as avg_price from product_group ) select [group], total_price from product_group pg inner join product_avg_price pap on pg.total_price >= pap.avg_price;
没有了重复, 有了结构, 可读性大大提升.
递归 parent child tree
CTE 还有一个常用的地方是递归找出所有子层.
go create table category ( id int identity primary key, name nvarchar(256) not null default '', parentId int null ); alter table category add constraint fk_category_category_parentId foreign key (parentId) references category (id) on delete no action; insert into category (name, parentId) values ('category root', null), ('category layer 1 a', 1), ('category layer 1 b', 1), ('category later 2 aa', 2), ('category later 2 ba', 3), ('category later 3 aaa', 4), ('category later 3 aab', 4); go
需求是找出 category layer 1 a 旗下的所有 category
with loop_category as ( select id, name, parentId from category where name = 'category layer 1 a' union all select c.id, c.name, c.parentId from category c inner join loop_category lc on c.parentId = lc.id ) select * from loop_category;
关键就是 with as 里面可以 unial all loop_category 实现递归.
相关文章
- RDS SQL Server - 专题分享 - 巧用执行计划缓存之Table Scan
- MySQL远程连接丢失问题解决方法Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0
- 【转】SQL Server -- 已成功与服务器建立连接,但是在登录过程中发生错误
- 细说SQL Server中的加密【转】
- SQL Server 函数之日期格式化函数
- sql server的备份
- 004.Zabbix3.x-Server服务端安装
- SQL Server – Work with JSON
- SQL SERVER (MSSQL) 学习笔记 性能
- sql server 我常用的语句
- server.port 在单元测试中,调用的类或者方法这个地方获取到的端口号就会变成-1
- CentOS7 和 CentOS8 安装 rusers-server 然后使用LR 实时监控Linux主机性能的办法
- 关闭SQL Server 数据库所有使用连接
- 使用SQL-Server分区表功能提高数据库的读写性能
- Eclipse报错:Setting property 'source' to 'org.eclipse.jst.jee.server:test1' did no
- SQL Server运维常用语句
- sql server 性能优化方法
- SQL Server日常维护常用的一些脚本整理
- git push代码失败Invocation failed Unexpected end of file from server
- SQL Server 动态生成分区脚本
- 谈谈基于SQL Server 的Exception Handlingp[下篇]
- 通过SQL SERVER添加系统管理员帐号
- Sql Server系列:数据库组成及系统数据库
- [SQL Server 2014] 微软将于年底发布新版数据库SQL Server 2014
- visualSVN server库迁移
- sql server 2008空间释放
- Install TightVNC Server in RHEL/CentOS and Fedora to Access Remote Desktops