SQL SERVER2008 多表分页显示 尾巴显示汇总

admin

SELECT *
FROM (
    SELECT a.ID, a.店号,a.店名,a.电子商务帐号,b.烟草档位,b.订货日期,b.帐户余额,b.订单金额,b.总购买数,b.状态,b.上传时间,a.城市,
           ROW_NUMBER() OVER (ORDER BY a.ID) AS RowNum
    FROM  门店信息 a,订单汇总 b
    WHERE a.电子商务帐号=b.电子商务帐号 and   b.状态 like '%成功%'  and b.上传时间>='2023-08-28 00:00:00' and b.上传时间<='2023-08-28 23:59:59' and(a.店号 like '%%' or a.店名 like '%%' or b.电子商务帐号 like '%%')
and a.城市='合肥市烟草'
) AS SubQuery
WHERE RowNum BETWEEN 2 AND 3;






SELECT *
FROM (
    SELECT a.ID, a.店号,a.店名,a.电子商务帐号,b.烟草档位,b.订货日期,b.帐户余额,b.订单金额,b.总购买数,b.状态,b.上传时间,a.城市,
           ROW_NUMBER() OVER (ORDER BY a.ID) AS RowNum
  , COUNT(*) OVER() AS 总条数  FROM  门店信息 a,订单汇总 b
    WHERE a.电子商务帐号=b.电子商务帐号 and   b.状态 like '%成功%'  and b.上传时间>='2023-08-28 00:00:00' and b.上传时间<='2023-08-28 23:59:59' and(a.店号 like '%%' or a.店名 like '%%' or b.电子商务帐号 like '%%')
and a.城市='合肥市烟草'
) AS SubQuery
WHERE RowNum BETWEEN 1 AND 10;