请问sqlserver05用row_number进行分页，如何获取记录总数？

[li2422121715]

With TTTTT AS
(
    select productid,productname,price,pictureshow,updatetime from product A 
    left join (select kindid,brandid from Kind) B on A.kindid=B.kindid
    where B.brandid=@brandid
)
select * from
(
    select ROW_NUMBER() OVER (ORDER BY updatetime desc) as rownumber,* from TTTTT
) as T
where rownumber between 1 and 10

这是我的sql语句，我想获得TTTTT记录的总数，求教

[chuanzhang5687]

自己另外在查询一次，然后关联到表里面

[莱昂纳多达芬奇]

With TTTTT AS ( select productid,productname,price,pictureshow,updatetime from product A left join (select kindid,brandid from Kind) B on A.kindid=B.kindid where B.brandid=@brandid ) select * from ( select ROW_NUMBER() OVER (ORDER BY updatetime desc) as rownumber,*,count(*) from TTTTT ) as T where rownumber between 1 and 10

[li2422121715]

引用 1 楼 chuanzhang5687 的回复:

自己另外在查询一次，然后关联到表里面

能不能利用已经排好序的rownumber？可以少一次连接查询

[li2422121715]

引用 2 楼 davinciyxw 的回复:

With TTTTT AS ( select productid,productname,price,pictureshow,updatetime from product A left join (select kindid,brandid from Kind) B on A.kindid=B.kindid where B.brandid=@brandid ) select * from ( select ROW_NUMBER() OVER (ORDER BY updatetime desc) as rownumber,*,count(*) from TTTTT ) as T where rownumber between 1 and 10

这样岂不是查询了N次count(*),而且每条记录里面都有记录总数。我只需要另外一个单独的count(*)

[叶子]

嵌套子查询即可。

With TTTTT AS
(
    select productid,productname,price,pictureshow,updatetime from product A 
    left join (select kindid,brandid from Kind) B on A.kindid=B.kindid
    where B.brandid=@brandid
)
select * from
(
    select ROW_NUMBER() OVER (ORDER BY updatetime desc) as rownumber,*,(select count(*) from TTTTT) as cnt from TTTTT
) as T
where rownumber between 1 and 10

[li2422121715]

引用 5 楼 maco_wang 的回复:

嵌套子查询即可。

With TTTTT AS
(
    select productid,productname,price,pictureshow,updatetime from product A 
    left join (select kindid,brandid from Kind) B on A.kindid=B.kindid
    where B.brandid=@brandid
)
select * from
(
    select ROW_NUMBER() OVER (ORDER BY updatetime desc) as rownumber,*,(select count(*) from TTTTT) as cnt from TTTTT
) as T
where rownumber between 1 and 10

这样select count(*)执行多少次？我在想会不会执行了N次

[li2422121715]

引用 5 楼 maco_wang 的回复:

嵌套子查询即可。

With TTTTT AS
(
    select productid,productname,price,pictureshow,updatetime from product A 
    left join (select kindid,brandid from Kind) B on A.kindid=B.kindid
    where B.brandid=@brandid
)
select * from
(
    select ROW_NUMBER() OVER (ORDER BY updatetime desc) as rownumber,*,(select count(*) from TTTTT) as cnt from TTTTT
) as T
where rownumber between 1 and 10

这样子执行，(select count(*)不会被执行多次吗，求指教

[水族杰纶]

--try
With TTTTT AS
(
    select productid,productname,price,pictureshow,updatetime ,
    count(*)over()总行数
    
from product A 
    left join (select kindid,brandid from Kind) B on A.kindid=B.kindid
    where B.brandid=@brandid
)
select * from
(
    select ROW_NUMBER() OVER (ORDER BY updatetime desc) as rownumber,* from TTTTT
) as T
where rownumber between 1 and 10