select top 5000 e.* from (select ROW_NUMBER() over(order by ID desc) as rownum,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code='undefined') as hbtype,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code=v.data) as datas,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code=v.YiChangStatus) as YCstatus,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code=v.TuiPiaoStatus) as TPStatus,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code=v.CaBianMaStatus) as castatus,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code=v.CaiGouStatus) as caigoustatuss,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code=v.kehubanliStatus) as kehustatus,
(select User_Name from T_Sys_Users where User_Id=v.Created_By) as CreateName,
(select User_Name from T_Sys_Users where User_Id=v.Last_Updated_By) as UpdateName,
(select login_name+' '+User_Name from T_Sys_Users where User_Id=v.ZeRenRen) as zerenrens,
(select login_name+' '+User_Name from T_Sys_Users where User_Id=v.CaBianMaRenGongHao) as cabianmaren,
FilenameCount = (select COUNT(*) from FileInfo o where o.TLabTGPendingRecordID=v.ID),
case when v.state=0 then '未处理'
when v.state=1 then '已处理'
when v.state=2 then '完成'
when v.state=3 then '失败'
when v.state=4 then '无航变'
else '未知'
end as HBStatus,* from T_Lab_TGPendingRecord as v) as e where rownum>50*(1-1) and e.state='0' and e.Enabled_Flag='Y' order by e.Creation_Date desc
加了条件得出结果导致与前面ID不一致怎么解决去求高手帮忙啊
------解决思路----------------------
;WITH t AS
(
select top 5000
--这两要把ID查出来,请自行添加
(select Function_Name from T_Sys_Dictionary_Details where Function_Code='undefined') as hbtype,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code=v.data) as datas,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code=v.YiChangStatus) as YCstatus,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code=v.TuiPiaoStatus) as TPStatus,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code=v.CaBianMaStatus) as castatus,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code=v.CaiGouStatus) as caigoustatuss,
(select Function_Name from T_Sys_Dictionary_Details where Function_Code=v.kehubanliStatus) as kehustatus,
(select User_Name from T_Sys_Users where User_Id=v.Created_By) as CreateName,
(select User_Name from T_Sys_Users where User_Id=v.Last_Updated_By) as UpdateName,
(select login_name+' '+User_Name from T_Sys_Users where User_Id=v.ZeRenRen) as zerenrens,
(select login_name+' '+User_Name from T_Sys_Users where User_Id=v.CaBianMaRenGongHao) as cabianmaren,
FilenameCount = (select COUNT(*) from FileInfo o where o.TLabTGPendingRecordID=v.ID),
case when v.state=0 then '未处理'
when v.state=1 then '已处理'
when v.state=2 then '完成'
when v.state=3 then '失败'
when v.state=4 then '无航变'
else '未知'
end as HBStatus,* from T_Lab_TGPendingRecord as v) as e where rownum>50*(1-1) and e.state='0' and e.Enabled_Flag='Y' order by e.Creation_Date DESC
)
select ROW_NUMBER() over(order by ID desc) as rownum, * FROM t