cte的用法

CTE 通用表表达式
概念:Common
Table Expression,简称CTE,中文可以叫做,通用表表达式.


用处:处理以前版本中SQL不好现实,不好理解,复杂的查询问题.比如:分页,递归查询...


基本用法:


WITH <name of your CTE>(<column names>)
AS
(
<actual query>
)

SELECT * FROM <name of your CTE>



示例一(基本用法):


with MyCTE(ID, Name)
as
(
   
select EmployeeID as ID, FirstName + ' ' + LastName as Name
   
from     HumanResources.vEmployee
)
select * from MyCTE
示例二(分页):


with MyCTE(ID, Name, RowID)
as
(
   
select EmployeeID as ID, FirstName + ' ' + LastName as Name,
             Row_Number()
over (order by EmployeeID) as RowID
   
from     HumanResources.vEmployee
)
select * from MyCTE where RowID between 1 and 10
示例三(关联CTE):


with OrderCountCTE(SalesPersonID, OrderCount)
as
(
    
select   SalesPersonID, count(1)
   
from     Sales.SalesOrderHeader
   
where     SalesPersonID is not null
    
group by SalesPersonID
)
select sp.SalesPersonID, sp.SalesYTD, cte.OrderCount
from OrderCountCTE cte inner join   Sales.SalesPerson sp
on cte.SalesPersonID = sp.SalesPersonID order by 3

示例四(使用CTE的删除):


CREATE TABLE Products (
   Product_ID
int NOT NULL,
   Product_Name
varchar (25),
   Price
money NULL,
CONSTRAINT PK_Products PRIMARY KEY NONCLUSTERED (Product_ID)
)
GO
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (1, 'Widgets', 25)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (2, 'Gadgets', 50)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (3, 'Thingies', 75)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (4, 'Whoozits', 90)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (5, 'Whatzits', 5)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (6, 'Gizmos', 15)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (7, 'Widgets', 24)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (8, 'Gizmos', 36)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (9, 'Gizmos', 36)
GO

--==================Delete duplicate products=============================
with DuplicateProdCTE
as
(
select Min(Product_ID) as Product_ID, Product_Name
    
from     Products
    
group by Product_Name
    
having count(1) >1
)
delete Products from Products p join DuplicateProdCTE cte
on cte.Product_Name = p.Product_Name and p.Product_ID > cte.Product_ID
示例五(递归查询):



CREATE TABLE Employee_Tree (Employee_NM nvarchar(50), Employee_ID int PRIMARY KEY, ReportsTo int)
--insert some data, build a reporting tree
INSERT INTO Employee_Tree VALUES('Richard', 1, NULL)
INSERT INTO Employee_Tree VALUES('Stephen', 2, 1)
INSERT INTO Employee_Tree VALUES('Clemens', 3, 2)
INSERT INTO Employee_Tree VALUES('Malek', 4, 2)
INSERT INTO Employee_Tree VALUES('Goksin', 5, 4)
INSERT INTO Employee_Tree VALUES('Kimberly', 6, 1)
INSERT INTO Employee_Tree VALUES('Ramesh', 7, 5)
--
with MyCTE
as
(   
select Employee_ID, Employee_NM, -1 as ReportsTo, 0 as SubLevel
   
from Employee_Tree where ReportsTo is null --root node
    union all
   
select e.Employee_ID, e.Employee_NM, e.ReportsTo, SubLevel +1
   
from Employee_Tree e, MyCTE where e.ReportsTo = MyCTE.Employee_ID

)
--select * from MyCTE
select MyCTE.Employee_NM as emp , MyCTE.SubLevel, e.Employee_NM as boss
from MyCTE left join Employee_Tree e on MyCTE.ReportsTo = e.Employee_ID
--OPTION(MAXRECURSION 3) --error
--
OPTION(MAXRECURSION 4) --ok
where SubLevel < 4
注意:
OPTION(MAXRECURSION 4)用来设置递归时查找的层数,默认是100,如果超过默认或指定的,则会报错.通常我们用一个层数列来过滤指定的层.




本文来自优快云博客,转载请标明出处:http:
//blog.youkuaiyun.com/htl258/archive/2009/04/25/4115298.aspx

### Kingbase 中 CTE(Common Table Expressions)的使用方法 CTE(Common Table Expression),即公用表表达式,是一种临时的结果集定义方式,可以在查询中多次重用。它通过 `WITH` 关键字引入,并允许递归调用自身来实现复杂的数据操作。 #### 基本语法 以下是 CTE 的基本语法形式: ```sql WITH cte_name AS ( SELECT ... FROM ... WHERE ... ) SELECT ... FROM cte_name WHERE ...; ``` - **`cte_name`**: 定义的公共表名称。 - **`AS (...)`**: 括号内的部分是一个标准的 SQL 查询,用于生成该 CTE 所表示的结果集。 - **后续查询**: 在主查询或其他 CTE 中可以引用此结果集。 #### 示例 1:非递归 CTE 假设有一个名为 `sales` 的表,存储销售记录,我们希望统计每个月的总销售额。 | sale_id | product_id | quantity | price | sale_date | |---------|------------|----------|---------|------------| | 1 | P001 | 5 | 20.00 | 2023-09-01 | | 2 | P002 | 3 | 15.00 | 2023-09-02 | | 3 | P001 | 7 | 20.00 | 2023-10-01 | ##### 查询语句 ```sql WITH monthly_sales AS ( SELECT EXTRACT(YEAR FROM sale_date) AS year, EXTRACT(MONTH FROM sale_date) AS month, SUM(quantity * price) AS total_amount FROM sales GROUP BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date) ) SELECT * FROM monthly_sales ORDER BY year DESC, month DESC; ``` 在此查询中: - **`monthly_sales`** 是一个 CTE,负责按年月汇总销售额。 - 主查询基于这个中间结果进行排序并返回最终数据[^1]。 #### 示例 2:递归 CTE 继续之前的员工管理例子,现在我们需要获取整个组织架构中的所有成员以及他们的层级关系。 ##### 查询语句 ```sql WITH RECURSIVE org_hierarchy(emp_id, manager_id, emp_name, level_num) AS ( -- 锚点查询:找到最顶层的节点(CEO) SELECT emp_id, manager_id, emp_name, 1 AS level_num FROM employees WHERE manager_id IS NULL UNION ALL -- 递归体:逐步扩展到下一层级 SELECT e.emp_id, e.manager_id, e.emp_name, oh.level_num + 1 FROM employees e INNER JOIN org_hierarchy oh ON e.manager_id = oh.emp_id ) SELECT * FROM org_hierarchy ORDER BY level_num ASC; ``` 在这个递归 CTE 中: - 初始阶段选取没有上级领导的记录作为起点; - 每次迭代都将当前层的所有直接下属加入结果集中,直到遍历完整棵树为止[^1]。 #### 性能考虑 需要注意的是,在不同版本之间,KingbaseES 对 CTE 的处理策略有所变化。例如,在 V8R3 版本中,CTE 默认会被完全物化成独立的物理表;而自 V8R6 起,则可以选择将其“内联”至主查询之中以获得更好的性能表现。 --- ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值