How can I use sub query to count the result before

2019-09-15 17:35发布

I have two queries on the same table which have identical where clause. This:

SELECT    qa.id, 
          qa.subject, 
          qa.category cat, 
          qa.keywords tags, 
          qa.body_html, 
          qa.amount, 
          qa.visibility, 
          qa.date_time, 
          COALESCE(u.reputation, 'N') reputation, 
          COALESCE(Concat(u.user_fname, ' ', u.user_lname), 'unknown') NAME, 
          COALESCE(u.avatar, 'anonymous.png') avatar, 
          ( 
                 SELECT COALESCE(Sum(vv.value),0) 
                 FROM   votes vv 
                 WHERE  qa.id = vv.post_id 
                 AND    15 = vv.table_code) AS total_votes, 
          ( 
                 SELECT COALESCE(Sum(vt.total_viewed),0) 
                 FROM   viewed_total vt 
                 WHERE  qa.id = vt.post_id 
                 AND    15 = vt.table_code limit 1) AS total_viewed 
FROM      qanda qa 
LEFT JOIN users u 
ON        qa.author_id = u.id 
AND       qa.visibility = 1 
WHERE     qa.type = 0
       OR amount IN ( :vals )
       OR date_time BETWEEN :s AND :e 
ORDER BY  $query_order
LIMIT     :j, 11;

And this:

SELECT COUNT(amount) paid_qs,
       COUNT(*) all_qs
FROM qanda qa
WHERE qa.type = 0
   OR amount IN ( :vals )
   OR date_time BETWEEN :s AND :e 

As you see, the second one just counts something. Also, where clause is really complex which takes lots of time to be executed.

Anyway, I want to combine those two queries. Something like this:

SELECT COUNT(amount) paid_qs, COUNT(*) all_qs, x.*
FROM (
    -- The first query should be here
) x
LIMIT :j, 11

But it doesn't work as expected. It returns one row. Do you know how can I combine those two first queries above?

0条回答
登录 后发表回答