mysql - 在计算行数时检索所有数据

Question

我需要检索表关注中的所有数据，并计算它将返回的所有行数。

当我不使用 count() 时，我的查询工作正常，它为我提供了所有行。

问题是当我添加 count(con_id) 它只返回一行。

如何检索所有数据，同时统计返回的行数？

表 user_info

user_id     fname      lname
1           Ano        Nymous
2           blank      blank   

表关注

con_id   user_id     con_subject     con_message     con_date      con_reply
1        1           server lag      pls fix it      2015-08-30    
2        1           help            pls fix it      2015-09-01    
3        2           blah blah       pls fix it      2015-09-02  
4        2           test            pls fix it      2015-09-03  
5        1           testt           pls fix it      2015-09-04    

这是我的查询:

SELECT fname,lname,con_id,user_id,con_subject,
con_message,con_date,con_reply, COUNT(con_id) AS 'returned_rows'
FROM concern
JOIN user_info
ON concern.user_id = user_info.user_id
ORDER BY con_date

这个的输出是

fname   lname   con_id  con_subject    con_message   con_date    con_reply  returned_rows 
Ano     Nymous  1       serverlag      pls fix it    2015-08-30             5

这是我想要的输出

fname   lname   con_id  con_subject    con_message   con_date    con_reply  returned_rows 
Ano     Nymous  1       server lag     pls fix it    2015-08-30             5
Ano     Nymous  2       help           pls fix it    2015-09-01
blank   blank   3       blah blah      pls fix it    2015-09-02
blank   blank   4       test           pls fix it    2015-09-03
Ano     Nymous  5       testt          pls fix it    2015-09-04

Answer 1

您可能想使用 SQL_CALC_FOUND_ROWS。但是，如果您想要查询本身中的列，您可以使用子查询和变量:

SELECT t.*, @rn as returned_rows
FROM (SELECT fname, lname, con_id, user_id, con_subject, con_message,
             con_date, con_reply, (@rn := @rn + 1) as rn
      FROM concern JOIN
           user_info
           ON concern.user_id = user_info.user_id CROSS JOIN
           (SELECT @rn := 0) params
      ORDER BY con_date
     ) t;

变量值是在子查询中计算的。然后在外部查询中使用它。