MSSQL获取第10-20行

debugcn 发表于 Dev

安东·史托平（Anton Styopin）

嗨，我想为我的Web项目中的分页程序提取数据库的行。

SELECT DISTINCT jtg.JobID, jtg.CreatedOn, ksnr.value as Snr, kkz.value 
as Kennz, kvu.value as Vu, kstr.value as Strecke, kkzvn.value as KennzVN, ttr.jobid1 as messageId 
FROM tbl_one jtg 
LEFT JOIN tbl_two ksnr on jtg.jobid=ksnr.jobid and ksnr.keyid=6 
LEFT JOIN tbl_two kkz on jtg.jobid=kkz.jobid and kkz.keyid=29 
LEFT JOIN tbl_two kvu on jtg.jobid=kvu.jobid and kvu.keyid=15 
LEFT JOIN tbl_two kstr on jtg.jobid=kstr.jobid and kstr.keyid=24 
LEFT JOIN tbl_two kkzvn on jtg.jobid=kkzvn.jobid and kkzvn.keyid=30 
LEFT JOIN tbl_three ttr on jtg.jobid=ttr.jobid2 
WHERE jtg.JobID IN (SELECT words.JobId FROM tbl_four words LEFT JOIN tbl_five keys on words.KeyID = keys.KeyID WHERE keys.Name = 'Schadennummer')

这是我的正常查询，效果很好。现在，我想获取onyl的特定行，例如10-20。

select * from (select row_number() over(order by jtg.jobid) num,* from 
tbl_Job_Tracking_Generator jtg) a 
where num > 10 and num < 21

这个查询很简单，给了我10-20行，但是我不能将其与上面的主要查询结合起来。香港专业教育学院尝试了很多事情，但做对了。希望你们能帮助我。

拉尔珀

您需要定义一个列，该列可用于通过Row_Number（）函数对SQL分页中的数据进行排序

这是一个样本

;with cte as (
SELECT 
    DISTINCT 
    ROW_NUMBER() Over (Order By jtg.JobID Desc) rn,
    jtg.JobID, 
    jtg.CreatedOn, 
    ksnr.value as Snr, 
    kkz.value as Kennz, 
    kvu.value as Vu, 
    kstr.value as Strecke, 
    kkzvn.value as KennzVN, 
    ttr.jobid1 as messageId 
FROM tbl_one jtg 
LEFT JOIN tbl_two ksnr on jtg.jobid=ksnr.jobid and ksnr.keyid=6 
LEFT JOIN tbl_two kkz on jtg.jobid=kkz.jobid and kkz.keyid=29 
LEFT JOIN tbl_two kvu on jtg.jobid=kvu.jobid and kvu.keyid=15 
LEFT JOIN tbl_two kstr on jtg.jobid=kstr.jobid and kstr.keyid=24 
LEFT JOIN tbl_two kkzvn on jtg.jobid=kkzvn.jobid and kkzvn.keyid=30 
LEFT JOIN tbl_three ttr on jtg.jobid=ttr.jobid2 
WHERE 
    jtg.JobID IN (
        SELECT words.JobId 
        FROM tbl_four words 
        LEFT JOIN tbl_five keys on words.KeyID = keys.KeyID 
        WHERE keys.Name = 'Schadennummer'
    )
)
select * from cte where rn between 11 and 20

另一方面，SQL Server 2012引入了具有偏移量的“按顺序排序”和“提取下一个”，以便在SQL中为数据库开发人员进行分页

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。