with as性能调优

Question

用with as查询出一个表。想要引用这个查询结果，比如对这个查询出来的表进行聚合，该怎么用呀。直接select 语句from（ with as 已经查好的表)，会报错，求大佬解答

Answer 1

with A as()
select 语句from A

WITH HH
AS
(SELECT ID FROM DEPT WHERE NAME LIKE '苏州%'
)
SELECT SUM(求和列) FROM
(
select * from proj where dept in (select dept from HH)
)