突然想到视图能做的事情,CTE也可以做,那么他们分别有什么区别和好处呢 ? 什么时候用到比较好。
另外, CTE语句可以用到javacode中作为SQL来进行查询吗???
测试数据如下:
if object_id('dbo.test') is not null drop table dbo.test
CREATE TABLE [dbo].[test](
col1 [nchar](10) NULL,
col2 [decimal] NULL,
col3 [nchar](10) NULL
) ON [PRIMARY]
insert into test
select 'shana', 123, 'male' union all
select 'kangna', 78, 'female' union all
select 'veney', 678, 'female' union all
select 'bagala', 456, 'male' union all
select 'sen', 235, 'male'-- 视图
alter view testView as
select col1 from testselect * from testView;-- CTE 查询
with CTE as (
select col1 from test
)
select * from CTE
另外, CTE语句可以用到javacode中作为SQL来进行查询吗???
测试数据如下:
if object_id('dbo.test') is not null drop table dbo.test
CREATE TABLE [dbo].[test](
col1 [nchar](10) NULL,
col2 [decimal] NULL,
col3 [nchar](10) NULL
) ON [PRIMARY]
insert into test
select 'shana', 123, 'male' union all
select 'kangna', 78, 'female' union all
select 'veney', 678, 'female' union all
select 'bagala', 456, 'male' union all
select 'sen', 235, 'male'-- 视图
alter view testView as
select col1 from testselect * from testView;-- CTE 查询
with CTE as (
select col1 from test
)
select * from CTE
CTE想要的时候就定义,用完就消失,不占用服务器资源