t-sql中with用法

with用来创建一个虚拟表

with 临时表名 as 查询得到的表 select * from 临时表名

 

with resultTable as (select *, row_number() over (order by ID desc) as RowNumber
from Staff_Positions_Infor where ZGBH in
(select ID from Staff_information where staff_name like '%张三%')) select * from
resultTable where RowNumber between 10 and 15 order by id desc

 

 

 上面查询也可以写成

select * from
(select *, row_number() over (order by ID desc) as RowNumber
from Staff_Positions_Infor where ZGBH in
(select ID from Staff_information where staff_name like '%张三%')) as 
resultTable where RowNumber between 10 and 15 order by id desc