-- =============================================
-- Author: 余波(杭州)
-- Create date: 2011/09/27
-- Description: 表表达式和CTE
-- =============================================
--表a1结构及数据如下
/*
id name
1 2
2 3
3 4
*/
---表表达式,外部指定列别名
select idname from (select ID from a1)as d(idname)
---表表达式,内部指定列别名
select idname from (select id as idname from a1)d
---表表达式不支持多引用
select * from (select id from a1)t join (select id from a1)t on t.id=t.id ---错误
select * from (select id from a1)t join (select id from a1)u on u.id=t.id ---正确
----cte中with前面最好用分号的原因是:with在T-SQL中还有其它用途,为避免歧义CTE with
----之前的语句必须用分号结束(但T-SQL并未强制要求)
----CTE和表表达式相同,也可以再内部和外部指定列别名
----内部
with cte1 as
(
select id as idname from a1
)select idname from cte1;
----外部
with cte2(idname) as
(
select id from a1
)select * from cte2
---多cte查询,最后可以引用上面任意一个cte
;with cte3 as
(select id from a1)
,
cte4 as
(
select id from cte3 where id>=2
)
select * from cte3 a left join cte4 b on a.id=b.id
表表达式和CTE
最新推荐文章于 2025-03-25 10:35:21 发布
