我有一串複雜的SQL，加上limit等參數取出該分頁的資料後，接著要重新取有總共有多少筆資料，主要是用在分頁功能上~

想透過正則表示式把我的複雜SQL取代成 SELECT COUNT(1) AS total_rows FROM ....的形式，想請問前輩們，要怎麼下正則表示式??

環境：php5.5 / mysql 5.5

比如說這個SQL

SELECT
    view_terms.dep_AS_value schAsName,
    view_terms.dep_kind_value schKindName,
    view_terms.dep_division_value schDepName,
    GROUP_CONCAT(DISTINCT CONCAT(classes.class_grade,'-',classes.class_room)) classRoom,
    if(wtplan.rv_opinion2<>'',wtplan.rv_opinion2,wtplan.rv_opinion1)opinion,
    GROUP_CONCAT(DISTINCT result ORDER BY result DESC) 'status',
    sum(perweekno*weeksno)cnt
FROM (SELECT class_id, class_division, class_grade, class_room
        FROM classes
        WHERE sch_id = '116' AND class_year = 109
        GROUP BY class_division
    ) classes
INNER JOIN view_terms ON `classes`.class_division = view_terms.dep_division_id
LEFT JOIN (
    SELECT *, (CASE WHEN giveup THEN -1 ELSE rvornot END) result
    FROM wtplan
    WHERE schid = '116' AND years = 109 AND giveup = 0 AND rvornot IN (0, 1, 3, 4)
    GROUP BY schdepid
) wtplan ON wtplan.schdepid = classes.class_division
GROUP BY classes.class_division

要如何在不影響from後面的sql之下，把 select ... from 中間的欄位(含子查詢)取代成COUNT(1) AS total_rows?

目前失敗的做法

// $this->sql : 上面那陀SQL

$sql = preg_replace('/\s{2,}/', ' ', $this->sql); // 替換掉空白
$sql = preg_replace('/(?:order|ORDER).*/s', ' ', $this->sql); // order之後的全部拿掉
$sql = preg_replace('/(?:LIMIT|limit|OFFSET|office)\s+\d+/', '', $sql); // 拿掉LIMIT|OFFSET (以防萬一regex不支援s參數)
$sql = preg_replace('/^(?:SELECT|select).+?(?:FROM|from)/s', 'SELECT 1 FROM', $sql); // 避免因重複欄位出錯
$sql = "SELECT COUNT(1) AS `total_rows` FROM ($sql) _pagination_"; // 因應可能有group，所以用新的select 包起來以防萬一

/*
此時SQL長這樣，出現錯誤的$sql
SELECT COUNT(1) AS `total_rows` FROM (SELECT
view_terms.dep_AS_value schAsName,
view_terms.dep_kind_value schKindName,
view_terms.dep_division_value schDepName,
GROUP_CONCAT(DISTINCT CONCAT(classes.class_grade,'-',classes.class_room)) classRoom,
wtplan.wtplanid,
if(wtplan.rv_opinion2<>'',wtplan.rv_opinion2,wtplan.rv_opinion1)opinion,
GROUP_CONCAT(DISTINCT result  ) _pagination_
*/

預期正則表示式處理的結果

SELECT
    COUNT(1) AS total_rows 
FROM (SELECT class_id, class_division, class_grade, class_room
        FROM classes
        WHERE sch_id = '116' AND class_year = 109
        GROUP BY class_division
    ) classes
INNER JOIN view_terms ON `classes`.class_division = view_terms.dep_division_id
LEFT JOIN (
    SELECT *, (CASE WHEN giveup THEN -1 ELSE rvornot END) result
    FROM wtplan
    WHERE schid = '116' AND years = 109 AND giveup = 0 AND rvornot IN (0, 1, 3, 4)
    GROUP BY schdepid
) wtplan ON wtplan.schdepid = classes.class_division
GROUP BY classes.class_division

2020/1/13 想到替代方法

//把原本的sql當作子查詢來處理
$sql = preg_replace('/\s{2,}/', ' ', $this->sql); // 替換掉空白
$sql = preg_replace('/(LIMIT\s+\d+\s)+(OFFSET\s+\d+)+$/', '', $sql); // 拿掉LIMIT|OFFSET (以防萬一regex不支援s參數)
$sql = "SELECT COUNT(1) AS `total_rows` FROM ($this->sql) _pagination_"; 

不過還是想知道如果要用regex來替換掉複雜sql中的
select[...,(select...from...limit 1)abc, ...]from...where...exists(select...from...)
中間的內容(中括號刮起來的地方)，要怎麼下正則??