with as 将查询出来的临时表联合查询

-------------将查询出来的临时表联合查询
with 临时表名 as(select * from emp where emp_name in(select emp_name from emp group by emp_name having count(*)>1))
select * from 临时表名 left join emp
on 临时表名.emp_no=emp.emp_no

with table1(tname) as(select top 1 emp_name from emp)
,table2(tname) as (select emp_name from emp where emp_no='002')
select * from table1,table2

declare @s nvarchar(3)
set @s = 't%'
;  -- 必须加分号
with
t_tree as
(
    select emp_name from emp where emp_name like @s
)
select * from emp where emp_name in (select * from t_tree)