with tmpPaging as
(select l_user_code,
vc_user_name,
row_number() over(order by t.rowid) num
from tg_user t
select t.*, (select count(1) from tmpPaging) totalcount
from tmpPaging t
where num between (2 - 1) * 20 + 1 and 2 * 20
order by num;
tg_user 表中记录为184条,不明白为何查询的结果中totalcount为41,而不是20或者184??哪位高人可以指点一下?
还有如何才能得到totalcount为184呢。
------解决思路----------------------
with tmpPaging as(
select l_user_code,
vc_user_name,
row_number() over(order by t.rowid) num,
count(1)over() totalcount
from tg_user t
)
select t.*
from tmpPaging t
where num between (2 - 1) * 20 + 1 and 2 * 20
order by num;