with dc as (
select PersonalInfo.*,PublicInfo.Sex as truesex ,ROW_NUMBER() over(order by personalinfo.update_time desc) as row from PersonalInfo inner join PublicInfo
on PersonalInfo.UserID = PublicInfo.UserID
where 1=1
and PositionTitle1 = 1010 or PositionTitle2=1010
and Specialty1 = '工科' or Specialty2 ='工科'
and edustate-1=32 or edustate & 32 =32
and Language1 = '英语' and Level1 = '四级'
or Language2 = '英语' and Level2 = '四级'
and Work_Year >=0
and Talent_Type = '普通求职'
and PublicInfo.Sex = '先生'
and YEAR(GETDATE())- PublicInfo.Birthday between 0 and 100
and Work_Place1 = '广东省'
or Work_Place2 = '广东省'
or Work_Place3 = '广东省'
or Work_Place4 = '广东省'
or Work_Place5 = '广东省'
and Loc_Now = '广东省'
and Loc_Reg ='广东省'
and Position1 in('','')
or Position1 in('','')
and Apply_Type = '全职' )select top 10 * from dc
where row>9000我要的是查询与分页效果
select PersonalInfo.*,PublicInfo.Sex as truesex ,ROW_NUMBER() over(order by personalinfo.update_time desc) as row from PersonalInfo inner join PublicInfo
on PersonalInfo.UserID = PublicInfo.UserID
where 1=1
and PositionTitle1 = 1010 or PositionTitle2=1010
and Specialty1 = '工科' or Specialty2 ='工科'
and edustate-1=32 or edustate & 32 =32
and Language1 = '英语' and Level1 = '四级'
or Language2 = '英语' and Level2 = '四级'
and Work_Year >=0
and Talent_Type = '普通求职'
and PublicInfo.Sex = '先生'
and YEAR(GETDATE())- PublicInfo.Birthday between 0 and 100
and Work_Place1 = '广东省'
or Work_Place2 = '广东省'
or Work_Place3 = '广东省'
or Work_Place4 = '广东省'
or Work_Place5 = '广东省'
and Loc_Now = '广东省'
and Loc_Reg ='广东省'
and Position1 in('','')
or Position1 in('','')
and Apply_Type = '全职' )select top 10 * from dc
where row>9000我要的是查询与分页效果
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,
newsid, topic, ntime, hits
FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC
select PersonalInfo.*,PublicInfo.Sex as truesex ,ROW_NUMBER() over(order by personalinfo.update_time desc) as row from PersonalInfo inner join PublicInfo
on PersonalInfo.UserID = PublicInfo.UserID
where 1=1
and PositionTitle1 = 1010 or PositionTitle2=1010
and Specialty1 = '工科' or Specialty2 ='工科'
and edustate-1=32 or edustate & 32 =32
and Language1 = '英语' and Level1 = '四级'
or Language2 = '英语' and Level2 = '四级'
and Work_Year >=0
and Talent_Type = '普通求职'
and PublicInfo.Sex = '先生'
and YEAR(GETDATE())- PublicInfo.Birthday between 0 and 100
and Work_Place1 = '广东省'
or Work_Place2 = '广东省'
or Work_Place3 = '广东省'
or Work_Place4 = '广东省'
or Work_Place5 = '广东省'
and Loc_Now = '广东省'
and Loc_Reg ='广东省'
and Position1 in('','')
or Position1 in('','')
and Apply_Type = '全职' )select * from dc
where row>@pagesize*(@pagenum-1)
and row<=@pagesize*@pagenum
with dc
这个对性能 好像有提高。
我基本是查不到with 的应用。
我知道怎样分页。我这个只是思路。
到时一改就行
只是想问下这个 存储过程怎么样?
主要是效率 方面,还有那些可以修改。
我这里 用了with
函数 就是那个year()
不用真不知道该怎样查询哦。
谢谢各位高手的指点