分页语句如下
SELECT *
FROM (SELECT A.*, rownum r
FROM (select pvt_visitid AS visitid,
pvt_visitorid AS visitorid,
max(pvt_referurl) as referurl,
TO_CHAR(min(pvt_visittime), 'hh24:mi:ss') AS visitdate,
max(t3.province_name) AS province,
max(t2.city_name) AS city,
MIN(pvt_ip) AS ip,
max(pvt_referdomain) AS referdomain,
max(pvt_visitnum) AS returnnum,
TO_CHAR(min(pvt_visittime), 'yyyy-mm-dd hh24:mi:ss') AS visittime,
MAX(pvt_keyword) AS keyword,
MAX(DISTINCT CASE WHEN pvt_enterflag = 1 THEN pvt_pageurl END) AS firsturl,
SUM(pvt_interval) AS times,
COUNT(*) AS pvs,
max(pvt_source) as source
from pd_pvtoday t1
LEFT JOIN pd_city t2 ON t1.pvt_areaid = t2.city_id
LEFT JOIN pd_province t3 ON t2.city_provinceid = t3.province_id
where TRUNC(pvt_visittime) = TRUNC(sysdate)
AND pvt_siteid = 20655
AND (-1 = -1 or Bitand(pvt_adtype, -1) > 0)
group by pvt_visitid, pvt_visitorid
ORDER BY referurl) A
WHERE rownum <= 20) B
WHERE r >= 1
这是第1页的分页数据,每页20条记录,出的数据如图1
然后看第2页的分页数据,每页20条记录,将rownum <= 40 where r >= 21,出的数据如图2
问题来了
1、除了第1条记录不一样,剩下的19条记录在第1页分页,第2页分页出现的数据居然是一样的
我都排序了,而且分页是按照rownum来算的,不应该这样啊
2、难道是因为referurl太多一样的原因?