计算并集的第二页结果

Question

我遇到了 SQLServer 问题。

我正在尝试弄清楚如何显示第二页结果。使用下面的查询提取第一页结果。

SELECT TOP 10 * FROM (
    SELECT c.id, c.name, c.inserteddate, c.cityname, ftblstates.name AS statename, clc.name AS catname, '' AS listingimagelogo, '' AS orgname, relocateyn, '' AS employerclassified
    FROM tblclassifieds c
    LEFT JOIN tblclassifiedscategories clc ON c.categoryid = clc.id
    LEFT JOIN ftblstates ON c.stateid = ftblstates.id
    WHERE c.expirydate != '' AND c.expirydate >= '5/17/2011'
    UNION ALL
    SELECT ce.id, ce.name, ce.inserteddate, suburb AS cityname, ftblstates.name AS statename, ce.jobtype AS catname, ce.listingimagelogo, ce.orgname, '' AS relocateyn, '1' AS employerclassified
    FROM tblclassifiedemployers ce
    LEFT JOIN ftblstates ON ce.stateid = ftblstates.id
    WHERE ce.expirydate != '' AND ce.expirydate >= '5/17/2011'
) a
ORDER BY inserteddate desc;

答案是否与下面类似？

SELECT TOP 10 * FROM (
    SELECT c.id, c.name, c.inserteddate, c.cityname, ftblstates.name AS statename, clc.name AS catname, '' AS listingimagelogo, '' AS orgname, relocateyn, '' AS employerclassified
    FROM tblclassifieds c
    LEFT JOIN tblclassifiedscategories clc ON c.categoryid = clc.id
    LEFT JOIN ftblstates ON c.stateid = ftblstates.id
    WHERE c.expirydate != '' AND c.expirydate >= '5/17/2011'
    UNION ALL
    SELECT ce.id, ce.name, ce.inserteddate, suburb AS cityname, ftblstates.name AS statename, ce.jobtype AS catname, ce.listingimagelogo, ce.orgname, '' AS relocateyn, '1' AS employerclassified
    FROM tblclassifiedemployers ce
    LEFT JOIN ftblstates ON ce.stateid = ftblstates.id
    WHERE ce.expirydate != '' AND ce.expirydate >= '5/17/2011'
) a

WHERE NOT a.id IN (SELECT TOP 10 tblclassifieds.id ...

ORDER BY inserteddate desc;

有人可以帮我完成查询吗？

Answer 1

一个简单的方法：

declare @page int = 2
declare @take int = 10

select * from (
    SELECT *, ROW_NUMBER() OVER(ORDER BY inserteddate desc) as rowno FROM (
        SELECT c.id, c.name, c.inserteddate, c.cityname, ftblstates.name AS statename, clc.name AS catname, '' AS listingimagelogo, '' AS orgname, relocateyn, '' AS employerclassified
        FROM tblclassifieds c
        LEFT JOIN tblclassifiedscategories clc ON c.categoryid = clc.id
        LEFT JOIN ftblstates ON c.stateid = ftblstates.id
        WHERE c.expirydate != '' AND c.expirydate >= '5/17/2011'
        UNION ALL
        SELECT ce.id, ce.name, ce.inserteddate, suburb AS cityname, ftblstates.name AS statename, ce.jobtype AS catname, ce.listingimagelogo, ce.orgname, '' AS relocateyn, '1' AS employerclassified
        FROM tblclassifiedemployers ce
        LEFT JOIN ftblstates ON ce.stateid = ftblstates.id
        WHERE ce.expirydate != '' AND ce.expirydate >= '5/17/2011'
    ) a
) b
where   rowno > (@page - 1) * @take 
    AND rowno <= @page * @take
order by rowno

我没有改变你的内部查询。

编辑：在 where 子句中添加括号