http://api.drupal.org/api/drupal/includes--pager.inc/function/pager_query/6Unfortunately, the rewrite rule does not always work as intended for queries that already have a "COUNT(*)" or a "GROUP BY" clause, and possibly for other complex queries. In those cases, you can optionally pass a query that will be used to count the records.原來問題出在這
For example, if you want to page the query "SELECT COUNT(*), TYPE FROM node GROUP BY TYPE", pager_query() would invoke the incorrect query "SELECT COUNT(*) FROM node GROUP BY TYPE". So instead, you should pass "SELECT COUNT(DISTINCT(TYPE)) FROM node" as the optional $count_query parameter.
druapl 官網的文件也有指出
遇到 sql 裡面有 count 或 group by 的確會有分頁數算不準的問題
由於我的條件還有 having
左思右想還是沒辦法解決
後來透過建 view 的方式解決了
sql 裡面有 Aggregation function 的，要另外在 pager_query 跟 theme 的 pager 塞算筆數的值
$sql_count = db_rewrite_sql("SELECT COUNT(*) FROM {vw_member_lv} WHERE nid = ".$nid." AND cts BETWEEN 1 AND 20");
$_result = pager_query("SELECT * FROM {ot_quest_trans} WHERE op IN ('a17', 'c35') AND nid = ".$nid." GROUP BY uid HAVING COUNT(uid) BETWEEN 1 AND 2", 10, 0, $sql_count);
........
$output = theme('table', $header, $rows, array('class' => 'member_list', 'cellpadding' => 0, 'cellspacing' => 0, 'border' => 0));
$output.= theme('pager', array(), $sql_count);
