alter PROCEDURE Job_GetPersonList
(
@pagesize int,
@cpage int,
@state int, --1表示全部
@count int output
)
AS
BEGIN
SET NOCOUNT ON;
with dc as
(
select p.Edu,hr.State, p.UserID,p.Sex,hr.PersonName,hr.PositionName,hr.Receive_Date,p.Work_Year, ROW_NUMBER() over(order by hr.Receive_Date desc) as row from
HR_Company_Receive as hr inner join
Users.dbo.PersonalInfo as p
on hr.PersonID = p.UserID
where p.State & @state = @state
)
-- select * from dc t where row between @cpage * @pagesize and (@cpage+1) *@pagesize
select top(@pagesize) * from dc t
where row =( select MIN(ROW) from dc where userid = t.UserID )
and t.row not in
(
select top(@pagesize*@cpage) row from dc t
where row =( select MIN(ROW) from dc where userid = t.UserID )
order by Receive_Date desc
)
order by Receive_Date desc
select @count = COUNT(1) from HR_Company_Receive as hr inner join
FPDUsers.dbo.PersonalInfo as p
on hr.PersonID = p.UserID
where p.State & @state = @state
END
GO
(
select p.Edu,hr.State, p.UserID,p.Sex,hr.PersonName,hr.PositionName,hr.Receive_Date,p.Work_Year, ROW_NUMBER() over(order by hr.Receive_Date desc) as row from
HR_Company_Receive as hr inner join
Users.dbo.PersonalInfo as p
on hr.PersonID = p.UserID
where p.State & @state = @state
)
-- select * from dc t where row between @cpage * @pagesize and (@cpage+1) *@pagesize
select top(@pagesize) * from dc t
where row =( select MIN(ROW) from dc where userid = t.UserID and row > @pagesize*@cpage )
order by Receive_Date desc
where row =( select MIN(ROW) from dc where userid = t.UserID and row > @pagesize*@cpage )
order by Receive_Date desc
就这句就将not in 那东西去掉。效率 也提高了。。谢谢