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
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
( 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
